Home/ Questions/Q 5498
Next
In Process
Computing Staff
  • 2
Asked: 2021-11-15T13:04:53+00:00 In:

Excel VBA To Format A Textbox

  • 2

Hi All
I’m going round in circles trying to solve this one.
I simply want to open an 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.

Thanks for your help.

Share

    1 Answer

    1. Try this:

      Sub FormatTextBox()
ActiveSheet.Shapes.Range(Array("TextBox1")).Select
  CharCount = Selection.Characters.Count
   With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, CharCount).Font.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0
    .Solid
   End With
End Sub

      This code can probably be cleaned up to eliminate the “Selection” but since this is just a snippet of a longer macro, I’m not going to make any other changes.

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0