assigning a macro to an activex command button

[PEACHY]

Help! Attempting to get a command button to run a simple pre-recorded macro. Zero experience of visual basic , office assistant telling me to type in the macro!!??!! Hello , what. Please help me or shoot me.Brain hurting.

 

[Andrew Poulsom]

If you CommandButton is from the Control ToolBox, right click it and choose View Code. You should something like this:

Private Sub CommandButton1_Click()

End Sub

You can copy your recorded code into there (excluding its Sub and End Sub lines) or run the existing Macro like this:

Private Sub CommandButton1_Click()
   Call Macro1
End Sub

where Macro1 is the name of your recorded Macro.

 

[Richie(UK)]

Please help me or shoot me.

BANG!

Seriously though, lets assume that you already have your recorded macro. If you press Alt&F11 you will be taken to the Visual Basic Editor (VBE). Here you should see, in the Project window, the VBAProject associated with your workbook. Under the project will be a folder called 'Modules' - double-click on this and it will show that it contains a module called 'Module1'. Within this module you will see the macro that you recorded, make a note of the name (probably Macro1).

Now, go back to the Excel window where you have added the Command Button from the Controls Toolbox. Double-click on the CommandButton and you will be taken back to the VBE between some lines of code like this:

Private Sub CommandButton1_Click()

End Sub

Between those lines write the name of your macro (Macro1). Alternatively, you can type 'Module1' immediately followed by a full stop (.) - this will then give you the option to choose from the macros contained in Module1.

Now go back to Excel and exit Desin Mode. That's it, whenever you click the button your routine will be executed.

HTH

 

[hesitall]

Tnx very helpfull even after all these years

 

[Luka_LB]

BANG!

Seriously though, lets assume that you already have your recorded macro. If you press Alt&F11 you will be taken to the Visual Basic Editor (VBE). Here you should see, in the Project window, the VBAProject associated with your workbook. Under the project will be a folder called 'Modules' - double-click on this and it will show that it contains a module called 'Module1'. Within this module you will see the macro that you recorded, make a note of the name (probably Macro1).

Now, go back to the Excel window where you have added the Command Button from the Controls Toolbox. Double-click on the CommandButton and you will be taken back to the VBE between some lines of code like this:

Private Sub CommandButton1_Click()

End Sub

Between those lines write the name of your macro (Macro1). Alternatively, you can type 'Module1' immediately followed by a full stop (.) - this will then give you the option to choose from the macros contained in Module1.

Now go back to Excel and exit Desin Mode. That's it, whenever you click the button your routine will be executed.

HTH

Thank you so much!!!

 

[srmammadov]

Hi,
I used macros for my activex controls button. But it works wrong when I click (copies the same thing), but works properly when I run macro's itself. How can I fix it?
code:

Private Sub CommandButton1_Click()
Sheets("Archive").Select
ActiveSheet.unprotect Password:="111"
Call Macro1
Sheets("Archive").Select
ActiveSheet.protect Password:="111"
End Sub

Thanks in advance

 

[Mr2017]

Thanks Richie (UK)!!

Clarifying that you need to exit design mode afterwards was useful, and saved me some time.

 

[slack7639]

Private Sub CommandButton1_Click()

' I have utilized the "Custom UI Editor For Microsoft Office", and made a Custom UI Ribbon
' The macro is called: Sub Process_Bookmarks(control As IRibbonControl)
' To get it to work here with the button, remove this part: control As IRibbonControl
' So, it will be renamed to: Sub Process_Bookmarks()

Call Process_Bookmarks

End Sub