Creating custom toolbar

[Lisa321]

I am trying to create a custom toolbar with macros I have written for a 6 step process I do weekly. I have my primary file where the macros are stored. The first step is to use a button on the toolbar to open the primary file, let's call it "master". After step 3, I save the primary file with a different name that includes the week as part of the filename, for example "wklyrpt10-01-11". This new file now contains all the same macros as the "master" file.



The next week, when I use the button on the toolbar to open the file, the macro that is assigned to the button is from the "wklyrpt10-01-11" file, not the "master" file and the subsequent steps don't work. How can I attach the macros to the toolbar so that they are always linked to the "master" file?


Thanks.

[GTO]

Hi Lisa,

Welcome to vbaexpress :-)

What year Excel are you working in? Also, could you show us the code you want the buttons to run, as well as any code you already have for building the commandbar, or, attach the wb?

Mark

[Lisa321]

Thanks for the welcome. Good to be here.

I am using excel 2003. I didn't write code to make my toolbar, I used the excel custom toolbar feature to create toolbar and button:

Open the file with the macro in it (in my case "master.xls)
Right click on the toolbar,
choose customize,
in toolbar tab choose new,
in commands tab choose macro,
drag a button to the toolbar,
choose modify the selection,
choose assign the macro
choose the macro to assign
(in my case master.xls!aOpenFile)


After many tries it appears that at this point the entire path\filename of the currently open file is linked to the button. The button works when master.xls is open. Then I rename the file to "week3.xls" and the button is tied to the renamed file. Next week, when I try to use my toolbar "week3.xls" opens when i really want "master.xls" to open.

Now after thinking about this more, it’s clear my error was in my approach. I brought my weekly data into my “master.xls” file and renamed the file. I think I need to leave the data in its own file, name it “week3.xls” and have both “master.xls” and the “week3.xls” files open. Then the buttons on the toolbar will work each week. I'll have to make some minor changes to my macros but I think it should work. I don't think I'm advanced enough to create my own toolbar with VBA.

[mikerickson]

Have you considered putting the code that the custom controls call in the Personal Macro Workbook?

[Lisa321]

Yes, but if I understand it correctly (and I'm just starting at the macro writing thing - so I'm still working on the understanding part), my personal macro workbook won't be available to someone else. Ultimately, I'd like to pass on this weekly task to a staff person and I'll be the backup. So I thought saving it all in one directory on our server would facilitate that.

[Lisa321]

The article "Simple Floating Toolbar" by lucas did the trick

After re-reading the article (with a clearer head) and finally understanding it, I figured this is very close to the answer I needed. I rewrote the sample code to fit my macros, ended up with six buttons and it works great. Thanks lucas!