Hiding Shapes When Not WordArt , or When WordArt Text <> "Draft"

[Carroll Rinehart]

Hello,

I have a spreadsheet that contains multiple sheets, and each sheet has
multiple shapes. Some of these shapes are WordArt, some textboxes,
arrows, etc. The shapes that are WordArt contain the text "Draft". I
have written VBA that cycles through each sheet, then each shape, to
hide them all. I don't however, want to hide the WordArt that contains
the text "Draft". I would like to be able to use an "IF/END IF"
paragragh to say, "IF not WordArt, then visible=false". Or else, I
would like to be able to say, "IF the shape does not contain the word
"Draft", then visible=false. I haven't been able to find a VBA
example using either technique. I have seen examples where you can
create WordArt, and add the proper text, but not using the text or the
type of shape to conditionally do something.

Thanks,

Carroll Rinehart
Social Security Administration

 

[Dave Peterson]

Maybe this'll help you:

Option Explicit
Sub testme()

Dim myShape As Shape
Dim myVisible As Boolean

For Each myShape In ActiveSheet.Shapes
myVisible = False
If myShape.Type = msoTextEffect Then
If InStr(1, myShape.TextEffect.Text, "draft", _
vbTextCompare) > 0 Then
myVisible = True
End If
End If
myShape.Visible = myVisible
Next myShape

End Sub

 

[Carroll Rinehart]

Worked like a charm. Thanks for your suggestion, Dave.

Carroll Rinehart