SELECTING TEXT BOXES

[mrbeanyuk]

Afternoon all!

Can anyone tell me if it is possible to select all Text Boxes in a spreadsheet?

Thanks!

 

[pgc01]

Hi

If the textboxes are embedded in the worksheet, construct a ShapeRange collection with them and select it. Use Shapes.Range and in the range specify an array with the names of the TextBox controls.

Ex.
ActiveSheet.Shapes.Range(Array("TextBox1", "TextBox2")).Select

Why do you want to select them?

Hope this helps
PGC

 

[mrbeanyuk]

[

Thanks very much! The reason I need to select all of them is because I have 200+ text boxes all with black text then my boss came along as asked if they could all by changed to red! Charming!

Thanks

 

[pgc01]

Hi again

200+ text boxes ???

This code sets text to red in all the textboxes of the active worksheet.

Please test it:

Sub TextBoxesRed()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    If shp.Type = msoOLEControlObject Then
        If shp.OLEFormat.ProgId = "Forms.TextBox.1" Then
            shp.OLEFormat.Object.Object.ForeColor = RGB(255, 0, 0)
        End If
    End If
Next shp
End Sub

Hope this helps
PGC

 

[mrbeanyuk]

Thanks very much. Sadly it did not work! I pasted it in and then ran it and nothing happened..

 

[erik.van.geit]

Hello,

pgc01's macro works for me
was the sheet with textboxes active when running the macro ?

I changed one line: so try this

Sub TextBoxesRed()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    If shp.Type = msoOLEControlObject Then
        If TypeName(shp.OLEFormat.Object.Object) = "TextBox" Then
            shp.OLEFormat.Object.Object.ForeColor = RGB(255, 0, 0)
        End If
    End If
Next shp
End Sub

alternatively
goto EDIT mode (leftop icon on CONTROLS-toolbar)
display properties window (next icon)
display drawing toolbar
Control+click all textboxes
in properties window change ForeColor

kind regards,
Erik