Spiky -
Thanks for the response, but is here is the part where i need the help. How
do i do that? (recording the macros and the button?).
Thanks for the help.
Oh, sure. I like to use the Visual Basic toolbar when recording for
ease of use. So open that up by right-clicking on a toolbar and
selecting it from the menu. I have Excel 2003, so all this applies to
that version and most earlier versions.
First, plan it out. Make sure you know each step you want so you can
do them easily. Don't do anything extra, or it will record that, too.
The one we've discussed is very short, so this shouldn't be a big
deal. If you want it to open both files for you, make sure they are
both closed before you start recording. If you want to have the one
file open and run the macro from there (I mean in the future), then
open that file and be looking at it before starting.
Second, you may need to adjust your Macro Security if you have never
used any before. I think that is a button on the toolbar, or it is in
the menu: Tools|Macro|Security. Choose medium security, probably. If
you have XL2007, there is a completely different way to do this.
Record:
1) Click the Record button on the toolbar. (red dot)
2) Give the macro a name and place to save. I don't think there can be
spaces in the name, so use underscore if you want a space. If you want
it just for your computer, choose Store in Personal Macro Workbook. If
you want it attached to the file so it can be used elsewhere, choose
This Workbook.
3) Do everything you need to do. Open file, hit F9 (calculate), save,
etc. Speed doesn't matter, but do it in the right order and don't miss
anything or add anything you don't want.
4) Click the Stop button. (same button, now a square)
5) If you store it in Personal Macro Workbook, Excel may ask to save
that when you quit Excel next. Be sure to say Yes.
Make a toolbar button (if you store in Personal Macro Workbook):
1) Right-click a toolbar and select Customize from the bottom of the
menu. Go to the Commands tab. In the first pane, select Macros. In the
second pane, drag the Custom Button to wherever you would like it on a
toolbar. Don't close the Customize window, leave it open for now.
2) Right-click on your new button and select Assign Macro. Choose your
new macro from the list.
3) You can also right-click and change the name of the button (you'll
see the name in tooltips in the future), change the picture on the
button, etc. if you like.
4) Now close Customize window.
Make a button in a file (if you store the macro in one of the files):
1) Open the Drawing toolbar by right-clicking on any toolbar.
2) Click on Text Box toolbar button. Draw a text box where you want
your macro button to be.
3) Type in whatever text you want.
4) Right-click on the text box. You may have to point at the very edge
of it to right-click. Select Assign Macro from the menu and choose
your macro.
5) Format the box however you want. Background color is very helpful,
Center-orienting the text makes sense, adjust the size, I like shadows
or 3D effect to make it actually look like a button, etc. (right-click
and select Format)
