assign macro to custom button 1

[cowski]

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.

 

[cowski]

One thing I forgot that might make a difference: I attached the custom toolbar to the workbook before doing the save as (or else it would not show up for other people).

 

[joerd]

You could try adding code to the Workbook Open event, to show the command bar and point the control to the right place.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[IRstuff]

I've never had that problem. You embed a button and assign it to Macro1. Unless you have hard definitions of the file name, it doesn't care what the filename is.

TTFN

 

[cowski]

IRstuff,
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.

 

[handleman]

cowski: I think you and IRstuff may be talking about different things... It sounds like you are actually adding a toolbar button up there by all the print, formatting, etc buttons, while IRstuff is talking about embedding a button down on the spreadsheet itself.

 

[cowski]

Yes, I am talking about a custom button on a custom toolbar (by open, save, print, etc). Hmmm, maybe an embedded button is the way to go.

 

[IRstuff]

Either way, same result.

TTFN

 

[Panars]

I am not certain, but I think I may know the problem.
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.

 

[cowski]

Oh the difference a day makes.

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?)

 

[joerd]

Cowski,
yes, you have to code it yourself. Here's an example:

Private Sub Workbook_Open()
    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

The button will run a Sub MyProcedure when you click it - assuming that you defined MyProcedure, of course ;-)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[cowski]

joerd,
Very helpful code, thanks for the tip!