CODEWITHSUNDEEP
excelvba
youcantryreachingme
youcantryreachingme

Reputation: 1155

Test whether shape is a specific shape ie button

I have a workbook with many shape objects. The shapes contain text. I'm using a script to emulate a text search. I've added two buttons - one for "search" (to run FindInShape2) and one to "clear" the formatting that was applied by the search (ie run ResetFont). (I've had to add On Error Resume Next just inside each loop). I have formatted the text on those buttons. My issue is that the "clear" script resets the formatting on the buttons.

To resolve this I'm looking for a test that is logically "if the current shape is not a button".

The local variable name for the shape object is shp. The key line populating this is:

For Each shp In ActiveSheet.Shapes

I need something like 

if shp.name not like 'Button%'

Note, the display texts on the buttons are "find" and "clear" but when I select the buttons they are named "Button 1" and "Button 2".

Any suggestions? Open to other methods of solving.

Upvotes: 0

Views: 908

Answers (1)

youcantryreachingme
youcantryreachingme

Reputation: 1155

Marucciboy2's comment put me on the path to an answer.

The code that works is:

If shp.Type <> 8 Then

In addition to the docs on the Shape Type property, the docs on msoShapeType enumeration map the type values (ie numbers) with descriptions of the shape types encountered.

In this case, type 8 matches the button controls.

As a bonus, the below will (crudely) aggregate the shape types encountered when looping through ActiveSheet.Shapes:

Dim sShapeTypes As String

For Each shp In ActiveSheet.Shapes
    sShapeTypes = sShapeTypes & ", " & shp.Type
Next

MsgBox sShapeTypes

If you have many shapes, the message box won't accommodate a long message. You can use an If statement to exclude some that you see appear in the message box.

If shp.Type <> 17 Then ' Ignores msoTextBox
    sShapeTypes = sShapeTypes & ", " & shp.Type
End If

Upvotes: 1

Related Questions