Get color code to Excel Textbox from cell reference in VBA

Question

I have the following VBA code in Excel which works fine

Sub Change_Color()

Color_TextBox4 = RGB(255, 255, 0)

Sheet03.Shapes("TextBox4").Select 
   With Selection.ShapeRange.Fill 
        .ForeColor.RGB = Color_TextBox4
    End With 
End Sub

However, I want that "Color_TextBox4" should source the color code from a cell in my Workbook as per below

Color_TextBox4 = Sheet03.Range("H1").Value`
Note: The text in cell H1 in Sheet03 = RGB(255, 255, 0)

The code then looks like

Sub Change_Color()

Color_TextBox4 = Sheet03.Range("H1").Value

Sheet03.Shapes("TextBox4").Select
    With Selection.ShapeRange.Fill
        .ForeColor.RGB = Color_TextBox4
    End With
End Sub

But when I run the code I get an error message

"Run-time error '13':
Type mismatch"

Do I need to declare/set "Color_TextBox4" in any specific way to make it work? Or what other changes do I need to do?

In the cell there is a text, which cannot be set to a value as an RGB function. Try to use Evaluate() to convert it to a number value. — Black cat
– Black cat, Commented May 24 at 12:23
@Black cat, thank you for your reply. I'm unfortunately not familiar with the Evaluate() function so can you please elaborate how/where I should us it? — jqx204s
– jqx204s, Commented May 24 at 12:38
Assume that H1 contains this text: 255,255,000 . Then the Evaluate function is something like this color_textbox4 = Evaluate("left(H1,3)+mid(h1,5,3)*256+right(h1,3)*256*256") ` — Black cat
– Black cat, Commented May 24 at 14:23

CDP1802 · Accepted Answer · 2025-05-24 12:54:31Z

0

Sub Change_Color()

    Dim s As String, a
    s = Sheet03.Range("H1").Value
    s = Replace(Replace(s, ")", ""), "RGB(", "")
    a = Split(s, ",")
    
    With Sheet03.Shapes("TextBox4")
        .Fill.ForeColor.RGB = RGB(a(0), a(1), a(2))
    End With
    
End Sub

answered May 24 at 12:54

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jqx204s May 24 at 13:02

thank you so much, your suggestion works perfectly :)

Lajos Arpad · Accepted Answer · 2025-05-24 13:11:18Z

The reason as of why

.ForeColor.RGB = Color_TextBox4

throws the error you have mentioned is that Color_TextBox4 is a text, whereas you need an RGB. Please don't convert it inside the With clause, but convert it prior to that. Like:

Sub Change_Color()

Color_TextBox4 = toRGB(Sheet03.Range("H1").Value)

Sheet03.Shapes("TextBox4").Select 
   With Selection.ShapeRange.Fill 
        .ForeColor.RGB = Color_TextBox4
    End With 
End Sub

and implement toRGB to convert your cell content into a viable RGB. If your cell code is numeric, then you can convert it to a number and be aware that this number is 256 * 256 * red + 256 * green + blue, so integer-division with 256 * 256 will give you the red part, green is integer division with 256 - red * 256 and green is the remainder of the division with 256.

MGonet · Accepted Answer · 2025-05-24 15:05:29Z

0

This is also the second way to complete. You need to define your own custom RGB function and then use Evaluate.

Function RGB(r As Long, g As Long, b As Long) As Long
    RGB = VBA.RGB(r, g, b)
End Function

Sub Change_Color()
    Dim s As String
    s = Sheet03.Range("H1").Value
     
    With Sheet03.Shapes("TextBox4")
        .Fill.ForeColor.RGB = Evaluate(s)
    End With

End Sub

answered May 24 at 15:05

MGonet

2,6102 gold badges3 silver badges9 bronze badges

Collectives™ on Stack Overflow

Get color code to Excel Textbox from cell reference in VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related