×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

assign macro to custom button

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.

RE: assign macro to custom button

(OP)
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).

RE: assign macro to custom button

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.

RE: assign macro to custom button

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



RE: assign macro to custom button

(OP)
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.

RE: assign macro to custom button

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.  

RE: assign macro to custom button

(OP)
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.

RE: assign macro to custom button

Either way, same result.

TTFN



RE: assign macro to custom button

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.

RE: assign macro to custom button

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

RE: assign macro to custom button

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

CODE

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 winky smile

Cheers,
Joerd

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

RE: assign macro to custom button

(OP)
joerd,
Very helpful code, thanks for the tip!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources