assign macro to custom button
assign macro to custom button
(OP)
Excel 2000 question:
I have a macro (a custom entry form, all logic is in the one form and a .bas file with one function - show the form) that I would like to assign to a custom button. This is easily done by customizing the button (assign macro...).
The problem comes if I do a "Save As...". Let's say I have file A.xls the button is assigned to ThisWorkbook.Macro1; now I save as and have file B.xls - now the button points to A.xls Macro1 instead of B.xls Macro1 (however, the keyboard shortcut I have set up points to the correct macro). This is a problem since the original file (template) is on my hard drive and want to 'save as' to the network to pass the file off to someone else to enter info. How do I get it to point to the correct macro after a save as?
I did a quick search and found thread770-57692 but it doesn't address my problem.
I have a macro (a custom entry form, all logic is in the one form and a .bas file with one function - show the form) that I would like to assign to a custom button. This is easily done by customizing the button (assign macro...).
The problem comes if I do a "Save As...". Let's say I have file A.xls the button is assigned to ThisWorkbook.Macro1; now I save as and have file B.xls - now the button points to A.xls Macro1 instead of B.xls Macro1 (however, the keyboard shortcut I have set up points to the correct macro). This is a problem since the original file (template) is on my hard drive and want to 'save as' to the network to pass the file off to someone else to enter info. How do I get it to point to the correct macro after a save as?
I did a quick search and found thread770-57692 but it doesn't address my problem.





RE: assign macro to custom button
RE: assign macro to custom button
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: assign macro to custom button
TTFN
RE: assign macro to custom button
That's how I thought it should work, I'll double check to make sure I didn't mess something up to start with.
joerd,
Thanks for the idea, if the step above doesn't turn anything up, I'll try it out.
RE: assign macro to custom button
RE: assign macro to custom button
RE: assign macro to custom button
TTFN
RE: assign macro to custom button
If the file works fine for someone else that opens it from the network (they open the file, a new toolbar pops up, they click the button and it works) then the solution for it to work on your computer is to delete your existing toolbar, open the file and a new toolbar should open up with a button that points to the correct location for the macro.
Once you have a custom toolbar installed on your machine, opening a file where that toolbar is attached does not re-open the toolbar. You have to manually delete it. Normally this does not cause a problem unless the filename or file location of the spreadsheet with the macro changes.
RE: assign macro to custom button
I deleted the custom toolbar and recreated it (only 1 button on it so far so no big deal) and now it works. I only wish I knew what options I used the first time around so I could avoid that in the future!
Thanks all for your replies.
ps to joerd - before I deleted I tried to record a macro to fix the button, but nothing got recorded. I guess you can't macro toolbar modifications? (or at least not with the record function, maybe you have to code those by hand?)
RE: assign macro to custom button
yes, you have to code it yourself. Here's an example:
CODE
Dim myButton As CommandBarButton
Set myButton = Application.CommandBars("Worksheet Menu Bar").Controls.Add
myButton.Caption = "My Button"
myButton.Style = msoButtonCaption
myButton.OnAction = "MyProcedure"
End Sub
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: assign macro to custom button
Very helpful code, thanks for the tip!