Solved excel VBA to format a textbox

Microsoft Excel 010 - complete package
March 10, 2015 at 03:14:51
Specs: Windows 7

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.


See More: excel VBA to format a textbox

Report •


#1
March 10, 2015 at 07:23:32
First, a posting tip, then I'll work on your issue.

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

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


Report •

#2
March 10, 2015 at 07:34:42
✔ Best Answer
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.


Report •

#3
March 10, 2015 at 10:23:48
I'm not sure why you reposted your OP exactly the same as you did the first time...I have deleted it.

Did you try the code I suggested in Response #2?

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

message edited by DerbyDad03


Report •

Related Solutions


Ask Question