Create and add macro in to an object

[whcmelvin]

Hi, how do i create an object and assign marco to the object automatically using VBA codes? is it possible?

 

[SydneyGeek]

Have you tried recording a macro while you do that? If so, what did you get?

Denis

 

[whcmelvin]

I recorded macro and it showed me the code below.
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=382.5, Top:=105.75, Width:=97.5, Height _
:=43.5).Select

_______________________________________________________________

What is did when i start recording is to create a button and double click the button to put msgbox"hi"

 

[SydneyGeek]

Assuming that the macro you want to trigger is Macro1, put this as the next line of the code --

    Selection.OnAction = "Macro1"

Denis

 

[whcmelvin]

They gave me an error msg.
Run time error 1004
unable to set on action property of the OLEOject Class
I already had a marco1 in my modules

 

[RoryA]

You do not assign macros to ActiveX buttons. Try recording a macro while adding a Forms button to the sheet - they do have an OnAction property.

 

[SydneyGeek]

You should get something like...

Sub ButtonPlusCode()
    ActiveSheet.Buttons.Add(158.25, 43.5, 75.75, 24).Select
    Selection.OnAction = "SomeMacro"
End Sub

Denis

 

[whcmelvin]

Thank you very much

 

[pgc01]

Hi

I think it is a good idea to name an object when you create it. It makes it easier to access it later.

Sub ButtonPlusCode()
Dim bt As Button
 
    Set bt = ActiveSheet.Buttons.Add(158.25, 43.5, 75.75, 24)
    bt.Name = "MyButton"
    bt.OnAction = "SomeMacro"
End Sub