Using VBA to change color of a ext box directly on worksheet

Roscoe · #1 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?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to change page color	gezz7louise	Word	2	11-06-2011 07:51 AM
Change bullet form and color	Vibe	PowerPoint	1	06-22-2011 05:29 AM
Change skin color of applications?	Jack R	Office	0	07-04-2010 04:25 AM
Change color of text in bullets	franklyorange	PowerPoint	2	06-22-2010 04:51 AM
Change Automatic Fill Color	Leanne	PowerPoint	1	11-04-2009 08:34 PM