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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.