Microsoft Office Forums - View Single Post - Using VBA to change color of a ext box directly on worksheet

Roscoe · 11-21-2011, 11:42 AM

I have a text box that I put directly on the worksheet. Not a programming object, but a straight text box. My VBA code messes with something and I want to alert the user on the sceen that something did not happen as expected by changing the text in the box as well as the color. It isn't necessariy a bad thing so I don't want to bother the user with a nag-box that forces him to acknowledge it (or go away and he forgets), I just want a red box visible that gets his attention.

I found a way to change the text:

Code:

thisworkbook.worksheets("main").Shapes("Test Box 1").textframe.characters.text = "text"

(this is probably way over-complicated but it was the only way I cold find to make it work)

However, no matter what I try I can't change the color. I found the following code online...

Code:

With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 45, 20)
     .Left = Range("B32:S33").Left
     .Top = Range("B32:S33").Top
     .Width = Range("B32:S33").Width
     .Height = Range("B32:S33").Height
     .Fill.ForeColor.RGB = RGB(0, 0, 0)
End With

...except that I don't want to create a box but rather use an existing one. I don't mid creating one if I new how to delete it later (This routine could run multiple times and I don't want to stack boxes on top of each other)

Ideas?