Assign a Macro to a Button

[mike575]

I can create a macro (Excel 2000) and it run fine. I can creat a button fine. How can i link the macro to the button. Instruction i printed from excel help sstates "Right click a selection handle for the button or graphic control, and then click ASSIGN MACRO on the short cut menu. I don't get this option. Any help please.

[jamescol]

Hi Mike - welcome to VBAX!

To manually link the button and macro, try clicking on your button from the toolbar. If nothing has been assigned to it, your should get a dialog box to assign a macro. This works in XLXP and XL2K3; I don't have XL2K to test.

Alternately, you can get to the right-click options stated in the help file to assign a new macro, or change the assignment to a new macro if one was previously assigned. The trick is to change the context of the right-click menu.

1. Right click the button and select Customize.
2. While the Customize dialog is displayed, right-click on your button again

The menu now displays options for working with the button's properties.

If you want to create the button and assign the macro using VBA, the following code is an example:

'This actually creates a new toolbar
Set myBar = CommandBars _
    .Add(Name:="ChangingButton", Position:=msoBarTop, _
    Temporary:=True)
'create the button on the new toolbar
'alternately, you can replace myBar.Controls to one of the built-in toolbars
Set oldControl = myBar.Controls _
    .Add(Type:=msoControlButton, _
    ID:=21)
'Assign the macro to the button
oldControl.OnAction = "myMacro"
'make the toolbar visible
myBar.Visible = True

Cheers,
James

[Anne Troy]

Hi from me, too, Mike.

If you've drawn, say, a textbox from the drawing toolbar, and want to assign a macro to it (I love this method myself--it seems so much easier than editing a command button or creating a toolbar button with code, yet it doesn't seem to lose the macro so easily)....

I always first LEFT-click the BORDER of the textbox, then right-click the border of the textbox. NOW, you should see "assign macro" appear. It won't appear if you click inside the textbox or if you don't click right on the border of the textbox properly...

In the above paragraph, graphic can be interchanged with textbox, except that graphics don't show borders on them...and you don't have to be so careful about selecting it.

It may also be helpful to make sure you FILL your textbox with a color first...

Let me know if a screenshot would be helpful.

[parry]

Hi Mike, there are 2 sets of controls you can use - those from the Forms toolbar and those from the Visual Basic toolbar (aka ActiveX controls). The Help you would have been looking at was for a Command Button drawn from the Forms toolbar where you have created an ActiveX Command Button. I prefer ActiveX controls as they give you far more options than the Forms controls.

To get to the Click event for the control open the VB Toolbar then click the design mode button. Now double click on the button and you will be taken to the VBE. You can either paste the code from your macro (exclude Sub and End Sub lines) into the Click macro or you can call it by using this line where MyMacro is the name of your procedure your wanting to run.

Private Sub CommandButton1_Click()
Call MyMacro
End Sub

[Zack Barresse]

I think I'm confused as to what button Mike is referring to. If it is a Forms toolbar command button, the easy way to assign a macro to it is just right click it. No need to click on the border, just right click. If you're looking to move it, then click on the border, or right click (to get the border to appear) then left click-n-drag from the border wherever you want. My 2?.

[parry]

Hi Zack, it wont be a control from the Forms toolbar as Mike said "Right click a selection handle for the button or graphic control, and then click ASSIGN MACRO on the short cut menu. I don't get this option. "

Only controls from the Forms toolbar have the assign macro option so it must be from the VB toolbar.

[Anne Troy]

Or it's a graphic, or a textbox or drawn object from the drawing toolbar...

[jamescol]

How about an object from a commandbar I think that covers the entire spectrum.

[lucas]

I think Parry has it right. The button was created using a button from the control toolbox toolbar. It doesn't give you the option of assigning a macro from the right click menu. You have to run the macro from the buttons code(on_click) which is located on the sheet that the button is created.

A button created from the forms toolbar will give you the option on right mouse click of assigning a macro.

Two completly different things allthough they are both buttons. At least thats what I think.

[parry]

Or it's a graphic, or a textbox or drawn object from the drawing toolbar...

Mike also said "I can creat a button fine." But a graphic/textbox/drawn object are not buttons are they?

jamescol - I'll conceed it could be a Command Bar button.

I think Parry has it right.

I always thought you were a clever and astute person Lucas.

If youve gathered anything from this Mike you will see Excel has many options in its trick bag. Put Anne et al out there misery and tell us what you did.

[mike575]

Its Sunday morning here in Wisconsin. I have read your comments, and after breakfast i will try some of the suggestions. The button I am trying to link to a Macro is a "Command Button Object" that was created under .
View, Toolbars, Control Toolbox, "Command Button". I work at it and provide additional feed back.

[mike575]

It works now.

Here is what I did 1 Created a new macro using the following commands. Tools, Macro, Record New Macro. I then cleared the contents and ran the macro from the macro control box it worked.

I then created a ?Command Button? as discussed in my previous thread.

Then I viewed the code page by right clicking on the worksheet Tab name. At this point I could find my macro under Project Explorer Module2. and my command button 1 that I created under a file called Sheet 6 (Sort16) as a Microsoft Excel objects. Once I entered the commands below my button worked.

Private Sub CommandButton1_Click()
Call Copy16
End Sub

I would like to thank all of you who answered my question and to parry who gave me an example of the above command.

I be back to learn more.

Mike in Wisconsin

[johnske]

Hi, I know this thread is marked as 'solved' - but I can't help adding a variation to the solution found. (This was lightly touched on by parry above)

Instead of having a macro (with code in it) and a button with separate code that is simply used to call the code in the macro - just go into the macro, copy or cut the body of the code and paste THIS as the code for the button...Is more direct and less error-prone that way.

[Ken Wright]

Mike, as anticipated by the others you have actually used a Control Toolbox button, instead of one from the Forms toolbar which is what the help file was referring to. If you wanted to try it, then simply do View / Toolbars / Forms, then click on the grey button on that toolbar, drag and create a button and you will now be able to follow the help instructions to the letter by right clicking and choosing 'Assign Macro' :-)

Regards
Ken................

[mike575]

I agree its solved: Ken Wright answer to use the button under FORMS gives me the options I need to create simple task macro and easily assign them to form buttons. Vs assigning the macro to command buttons then linking them to a macro.. With the form buttons I don?t have the relative reference problems that I had with command buttons.
Sorry for the delay in response i took a long weekend.