0

I am using this: How to create a dynamic button in excel

It creates a button and ties a macro to it and works well.

I can run it as a stand alone macro but if I try to call it 

Call CreateDynamicButton

Nothing happens, is it possible to call it?

Thanks

Sub CreateDynamicButton()
Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long


    Set MyR = Range("C110") 'just an example - you get that from your own script
    MyR_T = MyR.Top         'capture positions
    MyR_L = MyR.Left        '...
    'create button
    Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)

    'set main button properties
    With MyB
        .Name = "MyPrecodedButton"     'important - code must exist ... see below
        .Object.Caption = "MyCaption"
        .Top = MyR_T
        .Left = MyR_L
        .Width = 50
        .Height = 18
        .Placement = xlMoveAndSize
        .PrintObject = True            'or false as per your taste
    End With

End Sub

If - in advance - you have created following routine within the active sheet

Private Sub MyPrecodedButton_Click()
    MsgBox "Co-Cooo!"
End Sub
Community
  • 1
  • 1
xyz
  • 2,253
  • 10
  • 46
  • 68
  • This works for me. Have you tried stepping through the code where `Call CreateDynamicButton` and see what happens? – David Zemens May 17 '13 at 18:00
  • How are you trying to call it? Where is `Call CreateDynamicButton`? – Jon Crowell May 17 '13 at 18:19
  • Thank for the help, the problem turned out to be the macro before the Button Macro, it ran but failed to call the next Macro which was the button Macro. – xyz May 18 '13 at 16:12

1 Answers1

0

In your VB Editor, in the project explorer (left pane) right click your current project and choose Insert->module. Paste your code in the new module (module1).

Where ever you want your CreateDynamicButton sub routine to be called from type Call CreateDynamicButton.

Example to call from another button:

Private Sub CommandButton1_Click()
    Call CreateDynamicButton
End Sub
Ryan E
  • 499
  • 1
  • 7
  • 17
  • Ryan, Thank for the help, the problem turned out to be the macro before the Button Macro, it ran but failed to call the next Macro which was the button Macro. – xyz May 18 '13 at 16:13