Capture Forms button caption

[Otto Moehrbach]

Excel XP & Win XP
I have a number of Forms buttons on a sheet. I want to delete those buttons
that have a specific caption. To do that I must first capture the caption.
The following code is a simplification of what I need. It doesn't work
because the "Caption" line is not right. How would I write the code to
capture the caption? If I can't capture the caption, I could use the
assigned macro name. How would I do that?
Thanks for your time. Otto
Sub RemoveButtons()
Dim ShapeA As Shape
For Each ShapeA In ActiveSheet.Shapes
If ShapeA.Caption = "Doodle" Then _
ShapeA.Delete
Next ShapeA
End Sub

 

[Bob Phillips]

Otto,

Where are these buttons from?

If the forms menu, use

Sub RemoveButtons()
Dim ShapeA As Button
For Each ShapeA In ActiveSheet.Buttons
If ShapeA.Caption = "Doodle" Then _
ShapeA.Delete
Next ShapeA
End Sub


If from the control toolbox, use

Sub RemoveButtons()
Dim ShapeA As OLEObject
For Each ShapeA In ActiveSheet.OLEObjects
If ShapeA.Object.Caption = "Doodle" Then _
ShapeA.Delete
Next ShapeA
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[John Bundy]

Just to add a little to Bob's post, if you set both with UCase first, case
sensitvity won't be an issue.

Sub RemoveButtons()
Dim ShapeA As OLEObject
For Each ShapeA In ActiveSheet.OLEObjects
If UCase(ShapeA.Object.Caption) = UCase("Doodle") Then _
ShapeA.Delete
Next ShapeA
End Sub

 

[Otto Moehrbach]

John, Bob
Thanks for the help. That worked fine but now I found that most of the
button captions are multi-line so they have a Ctrl-Enter in them. I thought
a wildcard would work, like:
If ShapeA.Caption = "Next*" Then
but this doesn't work. Any ideas? Thanks for your time. Otto

 

[Bob Phillips]

Sub RemoveButtons()
Dim ShapeA As Button
For Each ShapeA In ActiveSheet.Buttons
If Left(LCase(ShapeA.Caption), 6) = "doodle" Then _
ShapeA.Delete
Next ShapeA
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Otto Moehrbach]

Bob
All I can say is "Why didn't I think of that?". Thanks. Otto