Excel VBA - Creating buttons dynamically with code assigned

Question

I am trying to create some buttons dynamically, and assign code to them.

The following code works

Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long


    Set MyR = Cells(i + 1, 11) '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

It creates the buttons within my loop. However, I want to assign something to the on click, so I use the following code

Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long


    Set MyR = Cells(i + 1, 11) '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
        .OnAction = "interpHere"
        .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

    Sub interpHere()
        MsgBox "hi"
    End Sub

I have basically added .OnAction = "interpHere" but when I run it, I get an error, unable to set the onaction property.

Where am I going wrong?

Is all your code in a Standard Module ??

Gary's Student
– Gary's Student

2019-04-27 10:23:39 +00:00
Commented Apr 27, 2019 at 10:23 — Gary's Student
– Gary's Student, Commented Apr 27, 2019 at 10:23
Here is a screenshot i.imgur.com/LFINtNC.png

BCLtd
– BCLtd

2019-04-27 10:29:02 +00:00
Commented Apr 27, 2019 at 10:29 — BCLtd
– BCLtd, Commented Apr 27, 2019 at 10:29

patel · Accepted Answer · 2019-04-27 11:04:52Z

1

try this code

Sub CreateButtons()
  Dim btn As Button
  ActiveSheet.Buttons.Delete
  Dim t As Range
  For i = 2 To 6 Step 2
    Set t = ActiveSheet.Cells(i, 3)
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
    With btn
      .OnAction = "interpHere"
      .Caption = "Btn " & i
      .Name = "Btn" & i
    End With
  Next i
End Sub

Sub interpHere()
    MsgBox "hi"
End Sub

edited Apr 27, 2019 at 11:04

answered Apr 27, 2019 at 10:59

patel

4401 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA - Creating buttons dynamically with code assigned

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related