Mass rename of macro buttons (2000 SP1a)

Topic

New Reply

I’ve got a user who has a couple of workbooks that have about 100 sheets each. He created a button on each sheet that runs a macro. Unfortunately, he wants to rename the button on all of the sheets. As far I know, Excel cannot find the button name on the find replace. Does anyone have any ideas on how to rename the buttons? I think he may have to manually rename each button.

Reply | Quote

Replies

Command buttons are part of the Shapes collection of a worksheet. If there is just one per worksheet, it will be named “Button 1” if it was created from the Forms toolbar (seems most probable from your description) or “CommandButton1” if it was created from the Control Toolbox toolbar.

This macro will rename buttons named “Button 1” to “cmdMacro”. Adapt as needed.

Sub RenameButtons()
Dim sht As Worksheet
On Error GoTo ErrHandler
For Each sht In ActiveWorkbook.Worksheets
sht.Shapes(“Button 1”).Name = “cmdMacro”
Next sht

ExitHandler:
Set sht = Nothing
Exit Sub

ErrHandler:
‘ No error if button of that name is not found.
If Err = -2147024809 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End If
End Sub

Reply | Quote

Thanks! I’ll have him run this macro on a copy of his workbook.

Reply | Quote