VBA: Assign macro to a button on a toolbar

**meherts** · 09-02-2010, 03:23 PM

How does one do this?

I only ask because I have a macro that will only execute if the template is located in a certain directory. If I move it, I have to reassign the macro.

I was hoping there would be a way, preferably through VBA, to assign this macro dynamically.

Thanks,
meherts

**meherts** · 09-02-2010, 04:16 PM

Well, I came up with a solution. If anyone has a better solution, feel free to post it!

Code:

Application.CommandBars(" ... ").Controls.Items( ... ).OnAction = ThisWorkbook.FullName & " ... "

Regards,
meherts

**MarvinP** · 09-02-2010, 04:17 PM

In the Tools -> Options under "Quick Access Toolbar"

I have a dropdown that shows my Macros. I can add any of my module macors to the QAT.

I don't know where to find this in Excel 2003. Any help here?

**meherts** · 09-02-2010, 05:16 PM

@MarvinP

I wanted to avoid having to manually set the macros, which the code I provided enabled me to do. As for your problem, it'll probably stand a better chance if you started a separate thread. Regardless, good luck!

Regards,
meherts

**romperstomper** · 09-02-2010, 05:20 PM

Safer to use:

Code:

Application.CommandBars(" ... ").Controls.Items( ... ).OnAction = "'" & ThisWorkbook.FullName & "'!macro_name_here"

in case the workbook name has spaces in it.

Thread: VBA: Assign macro to a button on a toolbar

Thread Tools

Display

VBA: Assign macro to a button on a toolbar

Re: VBA: Assign macro to a button on a toolbar

Re: VBA: Assign macro to a button on a toolbar

Re: VBA: Assign macro to a button on a toolbar

Re: VBA: Assign macro to a button on a toolbar

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions