Change font colour of a textbox

Question

I want to open an Excel file, go to the first sheet in the file, and change the text colour of textbox1 to red.

The only way I have managed to do it so far is via recording the macro.

It gives me

Workbooks.Open (fPath & sName)

            Sheets(1).Select

  ActiveSheet.Shapes.Range(Array("TextBox1")).Select

  With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 262).Font.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0
    .Solid
    End With

That's fine; however the length of the text is variable so I get an error with the code if it is less than the 262 characters above.

I tried to introduce

CharCount = Len(textbox1.Text)

However I get error 424 Object required

I initially tried

Sheets(1).Select
ActiveSheet.TextBox1.ForeColor = RGB(255, 0, 0)

but got error 438 Object doesn't support this property or method.

Answer 1

If you want to change the font colour of the entire textbox (i.e. not just certain characters) then skip the Characters method. Also you shouldn't rely on .Select, ActiveSheet and the likes. Set proper references instead.

This works:

Dim wb As Workbook
Dim ws As Worksheet
Dim s As Shape

Set wb = Workbooks.Open(fPath & sName)
Set ws = wb.Sheets(1)
Set s = ws.Shapes("TextBox 1")

s.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)

Answer 2

try this,

Sub Button2()
    Dim sh As Shape
    Set sh = Sheets("Sheet1").Shapes("Textbox1")
    sh.TextFrame.Characters.Font.Color = vbRed
End Sub