CODEWITHSUNDEEP
excelvbabuttonrename
Noldor130884
Noldor130884

Reputation: 994

VBA how to rename all buttons in a worksheet

I'm trying to execute a very simple procedure in which I would like all the buttons (copied from a worksheet to another) to be renamed as "Button " & number.

Somehow all the buttons (which are non-active-x) except the first 2 are renamed.

The code I wrote is:

Dim button As button
i = 1
For Each button In ws.Buttons
    button.Name = "Button " & i
    i = i + 1
Next button

Upvotes: 1

Views: 5405

Answers (2)

nollaf126
nollaf126

Reputation: 160

I also want to rename all the (NON-active x) buttons on my active worksheet. I would LOVE to access them by index, because as buttons are added, removed, changed, etc., I may have Code names "Button 3" and "Button 6" and no others. Consequently, accessing them by Code name is unreliable. Any solid info on how to change button captions via index would be soo helpful. Thanks so much!

Upvotes: 0

GSerg
GSerg

Reputation: 78185

Changing a collection while enumerating it is a bad idea more often than not. It would appear the name changes also affect the for each enumerator, so some buttons get skipped.

Try either of the following:

  • Accessing Buttons by index, not with For Each.
  • Copying all references to Buttons to an array (i.e. take a snapshot of the collection) and then looping through the array changing names.

Upvotes: 1

Related Questions