CODEWITHSUNDEEP
excelvbatogglebutton
Macronaute
Macronaute

Reputation: 196

Create a ToggleButton and change its caption programmatically on EXCEL VBA

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

Upvotes: 0

Views: 3328

Answers (3)

AlSkylark
AlSkylark

Reputation: 46

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.

Upvotes: 0

CLR
CLR

Reputation: 12289

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"

Upvotes: 2

Hawawa
Hawawa

Reputation: 141

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...

Upvotes: 0

Related Questions