Changing button names - [resolved]

[medison]

Here are a couple ways to identify the buttons. The first loop will work for buttons created from the Control Toolbox toolbar while the second loop will work for buttons created from the Forms toolbar.

It sounds like the buttons that you have are from the Forms toolbar. However, I would suggest that you replace them with buttons from the Control Toolbox. Instead of assigning a macro to the button you would add code to the button's click event. These buttons can be identified as CommandButtons as opposed to a generic msoFormControl type.


HTH

Dim o As OLEObject
    
For Each o In Sheet1.OLEObjects
    If TypeName(o.Object) = "CommandButton" _
     Then MsgBox o.Name, vbOKOnly, "Here's one!"
Next o
    
Set o = Nothing
    
'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
    
Dim s As Shape
    
For Each s In Sheet1.Shapes
    If Left(s.Name, 7) = "Button " And s.Type = msoFormControl _
     Then MsgBox s.Name, vbOKOnly, "Here's one!"
Next s
    
Set s = Nothing