Dynamically add toggle button to sheet and change it's name

Question

I'm using the code below to add a toggle button to sheet. I will need to dynamically re-create a sequence of buttons, and give them name and caption.

Can anyone help me with a way to change the caption/text of the toggle button button added using VBA? The bName will change so I will need a way to reference them by name.

Set Bttn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ToggleButton.1", Link:=False, _
                DisplayAsIcon:=False, Left:=R.Left, Top:=R.Top, Width:=R.Width, Height:=R.Height)
With Bttn
    .Name = bName
End With

where R is the target cell where button will be added

I tried the following, and they all error out:

Bttn.caption = bText
ActiveSheet.Shapes(bName).Text = bText

If you're getting errors it's useful to say what those errors are. — Tim Williams, Feb 13 '23 at 01:51

score 2 · Answer 1 · answered Feb 13 '23 at 22:02

2

Or, simply . . .

With Bttn
    .Name = bName
    .Object.Caption = "MyCaption"
End With

answered Feb 13 '23 at 22:02

Domenic

7,844
2
9
17

score 1 · Accepted Answer · answered Feb 13 '23 at 07:28

I found this workaround here: https://stackoverflow.com/a/37978572/9852011

Not sure why you need to do this exactly but this code should work for you:

Dim Bttn As OLEObject, aButton As Variant

Set Bttn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ToggleButton.1", Link:=False, _
                DisplayAsIcon:=False, Left:=R.Left, Top:=R.Top, Width:=R.Width, Height:=R.Height)
With Bttn
    .Name = bName
End With

Set aButton = Bttn.Object
aButton.Caption = "whateverYouWant"

Dynamically add toggle button to sheet and change it's name

2 Answers2