command buttons

[Guest]

Hi, I have a large number of command buttons and I need to change the name on
them, they all have the same name, I know that I can change the name by using
edit but I was wondering if there is a quick way to do it with more than one
at a time, I have about 300 buttons in two rows down two columns.

Thanks in advance, Wildauk.

 

[Tom Ogilvy]

Name or caption. (I assume caption)
each has their own property window.

you can do it with code if there is some pattern to your madness

Dim oleObj as Object
Dim cbtn as MSforms.Commandbutton
Dim sRoot as String, i as Long
sRoot = "BaseName"
i = 1
for each oleobj in Activesheet.OleObject
if typeof oleObj.Object is MSForms.CommandButton then
set cbtn = oleObj.Object
cbtn.Caption = sRoot & i
'cbtn.name = sRoot & i
i = i + 1
end if
next

If you change the name (not the caption), any event code will need to be
changed to reflect the new name.

 

[Guest]

Thanks Tom, excuse my ignorance but where would I put the code.

Wildauk.

 

[Tom Ogilvy]

sub RenameBoxes()
Dim oleObj as Object
Dim cbtn as MSforms.Commandbutton
Dim sRoot as String, i as Long
sRoot = "BaseName"
i = 1
for each oleobj in Activesheet.OleObjects
if typeof oleObj.Object is MSForms.CommandButton then
set cbtn = oleObj.Object
cbtn.Caption = sRoot & i
'cbtn.name = sRoot & i
i = i + 1
end if
next
End Sub

Save the workbook.

Alt+F11 to get to the VBE
then Insert=>Module

paste the code in there

Alt+F11 to get back to Excel

Tools=>macro=>macros, select RenameBoxes (sheet with boxes shoud be the
active sheet.)

Hit the Run button.

If it screws things up, close the workbook without saving.