Create a ToggleButton and change its caption programmatically on EXCEL VBA

Question

In my VBA code I create some new Sheets and I add buttons and a Togglebutton. I had no problem to do that but I want to change the ToggleButton caption without clicking on it.

I think it's possible only by writing code in the created sheet, I tried but I can't call my method because the sheet does not exist at the beginning of the code...

Have you any idea please ?

PS : Code for creating the ToggleButton with macro :

ActiveSheet.OLEObjects.Add(ClassType:=""Forms.ToggleButton.1"", Link:=False, DisplayAsIcon:=False, Left:=1195.5, Top:=77.25, Width:=87.75, Height:=100.5).Select

EDIT : My code is in a vba module

CLR · Accepted Answer · 2020-03-03 11:46:23Z

2

Hold the object as a reference, instead of Selecting it and then set the caption:

Set btn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ToggleButton.1", Link:=False, DisplayAsIcon:=False, Left:=1195.5, Top:=77.25, Width:=87.75, Height:=100.5)
btn.Object.Caption = "fred"

answered Mar 3, 2020 at 11:46

CLR

12.3k1 gold badge13 silver badges31 bronze badges

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

Comments

Hawawa · Accepted Answer · 2020-03-03 11:35:07Z

0

With your current code, you can just do Selection.Caption = "NewCaption" immediately after you create it (so you don't lose the selection)

I'd probably want to assign that togglebutton to an object variable though, in case you want to do anything with it further down the line. In fact, I'd try to avoid Select...

answered Mar 3, 2020 at 11:35

Hawawa

1411 gold badge1 silver badge6 bronze badges

3 Comments

Macronaute Over a year ago

When I do that, I got an error 438 : Object doesn't support this property...

Hawawa Over a year ago

What version of Excel are you using? I tried it on 2013 using a Checkbox and radiobutton, it works fine with those (Most similar items share a Caption attribute)

Macronaute Over a year ago

I'm using 2016 one, thank you for your help too Harry

AlSkylark · Accepted Answer · 2020-03-03 11:49:24Z

0

If it's only one Toggle Button (and you haven't changed it's default name) you can use the default index name to refer to it so you can change its caption.

So you would use:

ActiveSheet.OLEObjects("ToggleButton1").Object.Caption = "Your Caption"

If you have more than one control added with the Add method, and in general really, you'll have a better time assigning them to a variable and working from there.

answered Mar 3, 2020 at 11:49

AlSkylark

465 bronze badges

Collectives™ on Stack Overflow

Create a ToggleButton and change its caption programmatically on EXCEL VBA

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related