Part B: Making a Macro Do Simple Task
In this Part, you’ll learn to…
- record a macro
- automate a task
|
1) Recording Your First
Macro a) Select a Range on a Worksheet and then select Record New Macro...under Tools...Macro (shown in Fig 2.1)
|
(Figure
2.1)
| b) In the Record Macro dialog
box, type "SetBackgroundColor" in the Macro Name textbox to set the macro
name. Leave all other option by default then click the Ok
button. This will start the macro recording.
|
| c) In the Background Color
Panel, select the Light Yellow color box. This action will set the
background of the current selected Range in light yellow color.
|
d) To stop the macro recording, click
the Stop button (the navy blue rectangle) on the Macro Recorder toolbar.
|
You
have recorded a macro that set cell background to light yellow.
(Remember? You learn to do this in Part A) |
|
As the figure shows, the name of the sub routine is "SetBackgroundColor". The color index for the light yellow is 36. The background pattern is solid. (Note: The characters in Green with apostrophe in front are just comments and will be ignored by VBA. You can always insert comments in your code by doing so.)
a) On any worksheet, select a range. |
b) Run the recorded macro by select Tools...Macro...Macros... or press [Alt] and [F8] at the same time.
|
c) The Macro dialog box displayed. Since there is only one macro in the module, by default the only macro, SetBackgroundColor is selected. Click the Run button to run the macro. |
d) Cells will now have light yellow background color.
|
e) You can edit the macro. In this example we change the :ColorIndex = 3. When we run the macro, range selected will now have red background color. f) We can add a macro to a toolbar as well. (i) we create a command button by selecting View – Toolbars – Control Toolbox . Figure below display the Control Toolbox. Select the Command Button and embed it on the worksheet. (ii) After that right click on the button and and edit. Type Click Me.
|
| g) Right click on the command button and select
view Code and this will bring up the VBE. In the Code window, type
Module1.SetBackgroundColor
|
| h) Go back to the worksheet
and click on exit design mode (the highlighted icon shown below). This will add
the macro to the commandbutton. When you click on the button, it will run the
macro “Module1.SetBackgroundColor”
|
You can use the macro execute a repetitive by adding a line of code as …ActiveCell.Offset(1,0).Select… shown below.
When you click on the button, it will go down a row and set the cell color to light yellow.
3) Summary
This is how you record a macro, assign a macro to a command button and execute a repetitive task.
Previous | Back to Home | Back to TOC | Next