You must place the cursor in that cell before you can begin your "macro".
You'll see why later on.
 From
the Tools menu, select the Macro option.
Select
the Record New macro option.
A new window will open asking you for some information about the new macro. The
first box asks you for the name that you give to this macro. You can
also put a letter or a number in the shortcut box. You'll be able to execute the
macro by pressing theCTRL and a keys. You can place in any letter
or number that you want. The shortcut key is compulsory.
The window asks you if you want to store the macro in this file or in another
worksheet file. It's possible "to reuse" the macro commands in a personal macro
file. The same macro can then be used for several files. But this is only for
those that are really serious about using macros.
For the exercise:
Give a name to your macro. It should represent the actions that will be done
such as " Printing_the_budget" . The name of the macro cannot have any
spaces. An underline can be used to link words. It's also possible to have a
shortcut key to activate a macro command. This avoids you having to go into
the Tools menu, followed by the Macro, Macro commands, select the macro of your choice and pressing on the Execute button.
Enter
the data as shown in the picture above.
Press the OK button.
All the actions that you go to make will be added to the macro command until
you stop the record.
As soon as you press the OK button, the window disappears and a small
toolbar appears in its place. This small toolbar has only two buttons. The
first one is to stop the macro from record. The second is to activate or
deactivate the relative reference option. This can be important according to
the type of macro that you want to carry out. You do not activate this option
if you want the macro to do whatever you want it to do at always the same location.
You activate this option if you want the macro to start where the active cursor
is located. There will be more details of this option a little later on this
page.
Press
the second button to be sure that the relative option is activated. (It's very important for this demonstration).
Make
a block out of the A1 to C1 cells.
Press
the Fill button to change the cell's background color to the color of
your choice.
Press
the first button of the macro toolbar to stop the macro from record.
OR
From the Tools menu, select the Macro and Stop record options.
The new macro command is now complete. It could have had a lot more instructions
than this example. But this is only to demonstrate what a macro can do. It's
time to see if you may repeat it.
Place
the cursor in the A3 cell.
Press
theCTRL and A keys to activate the shortcut to the macro.
Here is the result of the macro. The cells of the third row have now the same
background color as the one you chose for the cells of the first row.
There is an explanation if it didn't work. You forgot to activate the relative
position option when asked. Excel will repeat the macro at the same location
instead of beginning it where the cursor is located. It's as for that reason
that it was asked to you to move the cursor in the A3 cell and to activate
the relative button.
Because the relative reference option was activated, you may execute
the macro to another place rather than where it was created. You just need to
put the cursor where you need to activate the macro. If the relative option
is not activated, the macro will always repeat itself at the same location.
It can be practical for your needs. It depends on the box. You decide when
you should activate the relative reference option or not. It's practical when
you know that you'll want to apply the macro to another place in your file.
For the last exercise, it was necessary to be able to apply it to the A3 to C3 cells.
A 1004 error message can also appear. Generally, it's because you forgot to
stop the macro command from record. The macro is then caught in an endless loop. The macro
is recalled before it's even finished! It will be necessary to change the macro.
Change a macro command
From
the Tools menu, select the Macro option
Select
the macro of your choice and press the Edit button.
The Visual BASIC editor will appear with the code of the macro command that
you want to change.
The macro indicates that three cells were selected (A1 to C1). It then indicates
the color as well as the pattern of the background of the selected cells.
Print a macro command
It's possible to print the code while changeing the macro command.
From
the File menu, select the Print option.
To return to Excel...
From
the File menu, select the Close and return to Microsoft Excel option.
Attach a macro command to a button
It's sometimes very interesting to be able to execute macro commands by just
pressing a button; even more if you leave your file to another person. They
probably don't know all the macro commands that you created to work faster. The next exercise consists in attaching a macro command to a button.
IMPORTANT: You must already have created a macro that you will need before attaching
it to a button.
From
the Edit menu, select the Toolbar option.
From
the list of the available toolbars, select the Forms toolbar.
 |
Move the toolbar if it's necessary.
Place
the cursor on the titles bar of the toolbar (the blue bar on top).
Press the left mouse button and move the toolbar towards the
place of your choice.
Once
the toolbar is at its new location, release the left mouse button.
This toolbar is used for creating forms in Excel files. It facilitates
the use of the model for the users. |
This toolbar offers you several objects, or controls, that you can place on forms. For the moment, you only have to worry about the button to
attach a macro command.
Press
the  button.
Press the left mouse button and determine the place and size
of the button.
When
satisfied with the size, release the left mouse button.
Excel will automatically ask you for the name of the macro that you want to
attach to the newly created button.
Select
the macro of your choice.
Press the OK button.
While
the button is still selected, you can change the text on the button to whatever
you need it to be.
Once
finished, click outside of the button.
To execute the macro that's now connected to the button.
Place the cursor in the A5 cell.
Place
the cursor over the button.
Press
the left mouse button.
The button now executes the macro. You can apply any macro that you make to a button. It's that easy!
If you wish to change the options of the button.
Place
the cursor over the button.
Press the right mouse button.
A context menu will appear next to the button.
If
you want to affect the presentation of this macro button, select the Format Control option.
The properties window of the button will allow you to change all the options
of your choice. All the possible options you will be found
under these seven tabs: Font, Alignment, Size, Protection, Properties, Margins and Web.
Under the Font tab you will find all the options for the presentation
of the text on the button. You can change the font, font style, size, color and effects.
The Alignment tab allows you to change the placement of the text inside the button including its'
orientation.
The Size tab allows you to determine exactly the size of the button
on the worksheet.
Like for the protection for cells, it's also possible to protect buttons under
the Protection tab. By default, all the buttons are protected when the
protection is activated. You should leave it protected unless you want the user to be able change the button
properties for some reason.
The Properties tab allows you to decide if the button should change
size and placement when you change you change you size and placement of the cells of the worksheet below it. You can also decide
to move or not the button if you insert or delete rows and columns. By default, the button
will not be printed unless you activate the Print the object option.
The Margins tab allows you to control the margin, or the space between
the text on the button and its border. You can use the predetermined margins
or change them to your choice.
You can always save this worksheet as a Web page. So Excel offers you some Web properties under the Web tab. For buttons, it only allows you to put some alternate
Assign a macro to a drawing
It's interesting, even practical, to place a macro on a command button. It's
easier for the users to use the options that you prepared them for them. But
these buttons lack originality. They're grey! That's why Excel also offers
you the possibility of placing a macro on a drawing. With a bit of work, these
drawings can have very interesting forms. Here are some examples.
Before being able to attach a macro to a drawing, you need two things: a drawing
and a macro. Let's presume that you already have both. The next part consists
only in attaching a macro to a drawing.
Place
the cursor over the drawing.
Press
the right mouse button.
From
the context menu, select the Assign macro option.
From
the list of macro commands that you have already prepared, select the one of
your choice.
Press
the OK button.
If you can't select the Assign macro option, click on the border of the drawing instead of the text inside it. You should then be able to assign the macro of your choice. You can repeat this operation on as many drawings as you want. It certainly puts
a little fun in your file!
Assign a macro to a toolbar
Assigning a button to a toolbar can be done in two
Create a new toolbar
From the View menu, select the options Toolbar and Customize.
Press the New button.
Enter the name you wish to give to your new toolbar and press the OK button.
An empty toolbar will appear on the screen. You can now drag any button that you wish to that toolbar; including macros.
Creating a button for the toolbar
Now is the time to add buttons that you will be able to place on any toolbar. You just create a new toolbar that has no buttons in it. This next step will show you how to add buttons to it, or to any toolbar.
Select the Commands tab.
From the Categories group, select Macros.
Drag the Custom Button to your new Toolbar.
The result is that you now have a button on your toolbar. You can drag other button onto it.
Close the Customize toolbar window.
The only thing left to do is to assign a macro to that new button.
Place the cursor over the button.
Press the right mouse button.
From the content menu, select the Assign Macro option.
From the list of available macros, select the one you wish to assign to the button.
Press the OK button.
Let's see if it works.
Place the cursor in the A7 cell.
Press on the button from the new toolbar.
Change button picture
You can now make macros, apply them where you want them and even create toolbars! But it's not over. A default button picture was placed on the toolbar when you added a macro command to it. Excel offers you a list of pictures you can use to better represent what your macro command will do.
Place the pointer over the new toolbar button.
Press the right mouse button.
From the context menu, select the Change Button Image option.
A list of pictures will appear. Select the picture that best represents the action from your macro.
The picture will be replaced. You can change again if you wish.
Edit button picture
The number of pictures you can select from is limited and may not answer your needs. So you can change the pictures or create your own.
Place the pointer over the new toolbar button.
Press the right mouse button.
From the context menu, select the Edit Button Image option.
The size of the picture and the number of colors is limited but not your imagination. You can try any form, put a shade on it, anything you can think!
Once you created the picture you wish, press the OK button.
You can now create you own macros and toolbars. This will help you personalize Excel to better answer you needs make you more efficient. That's what this site is all about! |
