PDA

View Full Version : Identifying textboxes with a certain text font color



cycle_simon
01-18-2008, 02:12 PM
I have a workbook with a number of textboxes on each sheet. The text font in each identifies the 'purpose' for the textbox. I've been trying to find a way to loop through all sheets, find any textboxes and if the text font used in the textbox is red (colorindex=3) then delete it.

The code loops through the sheets and textboxes but I have not been able to find a way to include the colorindex condition.



Sub Remove_StaffNotes()
Dim ws As Worksheet
Dim sh As Shape
For Each ws In ThisWorkbook.Worksheets
For Each sh In ws.Shapes
If sh.Type = msoTextBox Then
sh.Delete
End If
Next sh

Next ws
End Sub


:banghead:

Simon

ProteanBeing
01-18-2008, 02:30 PM
Try running a record macro while changing the text color of a shape. This should show you how to do this.

cycle_simon
01-18-2008, 03:15 PM
Thanks for the reply.

I had tried that. What it showed me was that the textbox was selected:



Activesheet.Shapes("TextBox 15").Select
With Selection.Font
.colorindex=3
End With



I was hoping to avoid setting up an index to loop through each possible textbox, activate it and verify the font color, since I am a VBA/programming newbie and this is already stretching my skills.

Would it be possible to include an "and" condition along the lines of:



If sh.Type = msoTextBox And sh.Font.Colorindex=3 Then
.....


Not being able to get this approach to work led me to post here. The "sh" variable doesn't reference the object properly, but I haven't been able to find what the right reference would be.

Thanks again.

cycle_simon

Bob Phillips
01-18-2008, 06:44 PM
Sub Remove_StaffNotes()
Dim ws As Worksheet
Dim sh As Shape
Dim I As Long

For Each ws In ThisWorkbook.Worksheets

For Each sh In ws.Shapes

If sh.Type = msoTextBox Then

With sh.TextFrame

For I = 1 To .Characters.Count

If .Characters(1, I).Font.ColorIndex <> 3 Then

Exit For
End If
Next I
If I > .Characters.Count Then tb.Delete
End With
End If
Next sh
Next ws
End Sub