Assigning a Macro to a Toolbar Button

[cmpgeek]

Ok yall - grab your butts cause if you dont, you are going to laugh them off when i tell you what i have done...

at some point in the last week, someone here was asking how to get Excel to open a brand new workbook by running a macro. I thought that was a neat concept and wanted to see if it would work... i had no intention of saving the workbook as i was just playing around with it...

apparently it did get saved because (normally) whenever i hit my macro button, it would open a window for me to choose what macro i wanted to run - if there were even any macros saved to that workbook... now whenever i hit that button it asked for a password to protect all worksheets... even if i hit cancel, it opens my census report file and tries to protect all the files...

I can not find the coding in order to remove it... i even removed the "protect all" codes trying to get it to stop... now all it does is open the census report and then tells me that the "protect all" coding is not there...

can someone PUHLEASE help me!

(im not really being mouthy, but i love this lil guy and havent been able to use him yet LOL)

[Zack Barresse]

ROFL! (Re your smiley)


Okay, just to be sure, can you post a picture of the button you are hitting? The shortcut button to Tools --> Macros --> Macros??

[cmpgeek]

ROFL! (Re your smiley)


Okay, just to be sure, can you post a picture of the button you are hitting? The shortcut button to Tools --> Macros --> Macros??

it is a custom button that used to open the same box as what opens when you manually choose Tools --> Macros --> Macros...


(as far as mr mouthy goes... i'm italian and talk with my hands all the time - maybe that is why i like him so much... its the little hands that just kill me... lol)

[Ken Puls]

Hi Nomi,

Are you using an ActiveX button, or a form button for this?

-If it's ActiveX (from the Control Toolbox) button, you should be able to right click it and choose "View Code"
-If it's a Form button (from the Forms toolbar), you should be able to right click it and say "Assign Macro"

Try finding out what macro it's calling and post the code here.

[Anne Troy]

And if it's a toolbar button, just drag it off while holding your Alt key and make a new one by following the instructs in Step 4: http://www.theofficeexperts.com/officevba.htm#ExcelVBA

[cmpgeek]

i am fixing to walk out the door (OT is not an option here) so i will get back to you both first thing in the morning...

thanks!

[Ken Puls]

Posted Today, 01:29 PM

ROTFL!

[Jacob Hilderbrand]

Sounds like your Toolbar Button got changed to a new macro. Click on Tools then Customize. Right click your button and select Assign Macro. Then choose the macro you are supposed to run.

[cmpgeek]

what i want it to do is open the box that would then show all the macros that are available on that workbook... i am going to try a couple things and see what happens...

wish me luck yall...

[Jacob Hilderbrand]

Do you mean this?

Application.SendKeys ("%{F8}")

[cmpgeek]

Do you mean this?
[vba]
Application.SendKeys ("%{F8}")
[/vba]

i dont know what that is... all i know is i added a custom button to my toolbar ages ago and that when i would click on it i would get the exact same box that shows up when going through Tools - Macros - Macros...

i hate being so obviously out of my element... im sorry yall

[Jacob Hilderbrand]

Make this Macro.

Sub MyMacro
Application.SendKeys ("%{F8}")
End Sub

Then assign it to your toolbar button.

[Anne Troy]

I think Nomi wants the box that pops up when you put a macro button on the toolbar. When you click on it, you're asked which macro you want to assign. Here's the steps at the link I gave. See the very last paragraph at the bottom first, and you'll understand.

Step 4: Assign the Macro to a Toolbar Button

Hit Tools-Customize and choose the Commands tab. The following dialog box appears:

Under the Categories (left), scroll down and choose Macros. Click on the smiley icon and drag it up to any location you choose on any toolbar you choose. Your mouse pointer must have a plus sign (+) hanging on it before you lift your finger from the mouse or the icon will not be placed. While dragging, you will see an X hanging on your mouse pointer.

Once your icon is placed on a toolbar, click the Modify Selection button, and various options will appear. The following describes only those options that apply for our purposes here:

Name: Type in the name of the macro as it should appear on your toolbar if you are going to use the text style button (described below).

Reset Button Image: Select this only if you have begun to edit an existing button image and you would like to reset it to the default.

Edit Button Image: Select this option to "draw" your own button image.

Change Button Image: Select this option to change the existing button image to one of your choosing from Office's collection of button images.

Default Style: Clicking this button makes the button on the toolbar show just the icon and no text.

Text Only (always): Clicking this button makes the button on the toolbar show just the text as it is shown in the Name box, which you can change.

Image and Text: Clicking this button makes the button on the toolbar show both the icon and the text.

When the button appears exactly as you would like it to appear on the toolbar, hit the close button on the Customize dialog box.

The first time you hit your new toolbar button, you are asked which macro you want it to run. You'll pick your new macro from the dialog that pops up.

[cmpgeek]

i figured it out... somehow (i admit - dont know how) a specific macro was attached to the button... when i opened the customize option and right clicked on the button and then choose "assign macro" there was one already there... as soon as i deleted that and closed it everything was fine... now when i hit the button, the dialog box comes up so i can choose...

if this day keeps going in this direction i'm gonna need a beer lol...

[Anne Troy]

Tho I don't think you are...if you want a shortcut key to run any macro you want, any time you want, you can hit Alt+F8, and double-click whichever one you want.

[cmpgeek]

Tho I don't think you are...if you want a shortcut key to run any macro you want, any time you want, you can hit Alt+F8, and double-click whichever one you want.

you know me so well... but thanks for telling me anyway!

[Zack Barresse]

It's the Alt sign ( % ). Here are some different keys:

+ = Shift
^ = Ctrl
% = Alt
~ = Enter

So if you wanted to open a file it would be ...

Application.SendKeys ("%fo")

The very tricky part about SendKeys is when you use them. It is very hard to debug with them. Because if you are stepping through your code in the VBE, then preesing Alt + F + O will not produce any results except activate the File menu (there is no shortcut of O in that menu). So be wary.