Finding form command buttons

[Glove303]

I have some code that searches for form command buttons by going through all the Shapes on a sheet, and checking their name.

This is not particularly efficient. The code looks like this:

Dim Sh As Shape

With Trades
For Each Sh In .Shapes
If Mid(Sh.Name, 2, 3) = "btn" Then
Sh.Delete
End If
Next Sh
End With

Is there a way to loop just through form command buttons?

 

[Rasm]

Yes- check this link

http://www.cpearson.com/excel/FindAll.aspx

 

[MickG]

You can do this :-

Buttons.Delete

Or this:-

Dim B As Button
For Each B In ActiveSheet.Buttons
   B.Delete
Next B

 

[Glove303]

You can do this :-

Buttons.Delete

Or this:-

Dim B As Button
For Each B In ActiveSheet.Buttons
   B.Delete
Next B

Will that skip activex buttons?

 

[AlphaFrog]

    Dim btn As Button
    
    With Trades
        For Each btn In .Buttons
            [COLOR="Green"]'Do something to each button[/COLOR]
            btn.Select
        Next btn
    End With
    
    [COLOR="Green"]'Delete all buttons[/COLOR]
    Trades.Buttons.Delete

 

[AlphaFrog]

Will that skip activex buttons?

Yes

 

[Glove303]

Great, thanks people.

So out of interest, how do a refer specifically to an activex command button?

 

[AlphaFrog]

    Dim oleCmb As OLEObject
    
    [COLOR="Green"]' Loop through each ActiveX command button[/COLOR]
    With Trades
        For Each oleCmb In .OLEObjects
            If TypeName(oleCmb.Object) = "CommandButton" Then
                [COLOR="Green"]' Do somthing with each ActiveX Command Button[/COLOR]
                oleCmb.Select
            End If
        Next oleCmb
    End With
    
    [COLOR="Green"]' Reference a command button by Name[/COLOR]
    Trades.OLEObjects("CommandButton1").Select

I don't think there is a built-in collection specific to ActiveX command buttons as there is for Forms type command buttons.