How To: Record Your Own Macro
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In a recent post, I showed how to copy someone else’s VBA procedure (i.e., “macro”) and paste it into your own workbook. Fortunately you don’t have to rely on others for custom macros. Excel includes a Macro Recorder which captures your actions, so that you can replay them later, saving yourself time and keystrokes.
It’s easy to record and rerun macros. You can start the Macro Recorder in a number of ways.
The Macro Recorder will now record all of your steps until you turn it off, using one of several methods.
- Tools menu > Macro > Stop Recording
- Stop Recording button on the Visual Basic toolbar
- Stop Recording button on the Stop Recording toolbar
To see your handiwork, go to the VB Editor (VBE). There are three ways to get there:
- Tools menu > Macro > Visual Basic Editor
- Visual Basic Editor button on the Visual Basic toolbar
- Shortcut key combo Alt+F11
If your workbook had no macros to begin with, the Macro Recorder creates a new code module, which you can see in th Project Explorer Window. If the Project Explorer isn’t visible, go to View menu > Project Explorer.
To view the macro you’ve recorded, double click on the new code module.
The macro can be run in a number of ways:
- Tools menu > Macro > Macros, select the Macro, and click Run
- Shortcut Key combo Alt+F8, select the Macro, and click Run
- Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
- Click in the macro with the mouse and press F5
This is the first exposure most people have to VBA programming. In a series of upcoming posts, I’ll cover the following VB topics:
- Fixing a recorded macro
- Assigning a macro to a button or shape, ActiveX button, or menu or toolbar
- VBE settings
- VBE features
- Debugging and error proofing
Possibly Related Posts:
- How To: Use Someone Else’s Macro
- How To: Assign a Macro to a Button or Shape
- How To: Fix a Recorded Macro
- How To: Assign a Macro to a Toolbar or Menu
- How To: Assign a Macro to an ActiveX Control
- Improved Macro Security Warning in Excel 2010
- Make Your Recorded Macro Independent of Which Sheet is Active
- Chart Event Class Module to Highlight a Series
- Highlight a Series with a Click or a Mouse Over
- PTS Charting Classes
Posted: Monday, March 10th, 2008 under VBA.
Comments: 2
Comments
Comment from Postal Bob
Time: Friday, October 17, 2008, 12:10 pm
I need instructions on how to program 1 cell on a worksheet so that when you click on it it deletes the data that has been entered in unprotected cells. Can this be done? I have to present this to my boss the end of next week and he’s a computerphob, the easier it is the better he likes it. Thanks
Comment from Jon Peltier
Time: Friday, October 17, 2008, 12:57 pm
Bob -
Here’s a procedure that will clear unprotected cells in the active sheet. Place it in a regular module in the workbook.
Sub ClearUnlockedCells()
Dim r As Range
Dim iCalc As Long
Application.ScreenUpdating = False
iCalc = Application.Calculation
Application.Calculation = xlCalculationManual
For Each r In ActiveSheet.UsedRange.Cells
If Not r.Locked Then
r.ClearContents
End If
Next
Application.ScreenUpdating = True
Application.Calculation = iCalc
End Sub
Don’t use a cell as the trigger, open the Forms toolbar, and stick a button on the sheet. When the Assign Macro dialog pops up, select the above macro. Then change the caption on the button to something like “Clear Inputs”. Then protect the sheet.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.