assign RGB color that is stored in a variable, to a label or other object

[katana_flyer]

I have a variable that get's it value from a cell. I would like to use that variable to assign colour to objects.... this is what I have, which doesn't work, and you pros will know exactly why I'm sure.

In cell "a1" is a string of text RGB (255,255,0)

Is it possible for ForeColor to accept it as as an RGB value, via a variable ?
Thank you....

Dim team1colorscheme As String
team1colorscheme = Range("a1").Value

Me.team1player1list.ForeColor = team1colorscheme

 

[mikerickson]

To translate the string "RGB(155,255,0)" to an RGB value.

Dim aString as String, rVal as Double, gVal as Double, bVal as Double
aString = CStr(Range("A1").Value) & "(0,0,0"

rVal = Val(Split(aString , "(")(1))
gVal = Val(Split(aString, ",")(1))
bVal = Val(Split(aString, ",")(2))

Me.TeamListPlayer1list.ForeColor = RGB(rVal, gVal, bVal)

 

[lrobbo314]

Same idea using regex.

Book1
	A
1	RGB (255,255,0)
2
3	test text
Sheet1

Sub rgbString()
With CreateObject("VBScript.RegExp")
    .Pattern = "(?:(\d{1,3}))"
    .Global = True
    Set matches = .Execute(Range("A1").Value)
    Range("A3").Font.Color = RGB(matches(0), matches(1), matches(2))
End With
End Sub

 

[katana_flyer]

To translate the string "RGB(155,255,0)" to an RGB value.

Dim aString as String, rVal as Double, gVal as Double, bVal as Double
aString = CStr(Range("A1").Value) & "(0,0,0"

rVal = Val(Split(aString , "(")(1))
gVal = Val(Split(aString, ",")(1))
bVal = Val(Split(aString, ",")(2))

Me.TeamListPlayer1list.ForeColor = RGB(rVal, gVal, bVal)

To translate the string "RGB(155,255,0)" to an RGB value.

Dim aString as String, rVal as Double, gVal as Double, bVal as Double
aString = CStr(Range("A1").Value) & "(0,0,0"

rVal = Val(Split(aString , "(")(1))
gVal = Val(Split(aString, ",")(1))
bVal = Val(Split(aString, ",")(2))

Me.TeamListPlayer1list.ForeColor = RGB(rVal, gVal, bVal)

Thank you, worked great, although I admit I'm having a bit of a tough time understanding why.... I'm not sure about the Val(split) parts and the 1's and 2 and the end of it... and the CStr part and why there are the 0's at the end...

 

[mikerickson]

Split takes a delimited string and returns a 0 based array.

myArrry = Split("a/b/c", "/) returns the array {"a", "b", "c"}. It is zero based so myArray(0) = "a", myArray(1) = "b",...

The CStr part is there, because I never trust what might be in a cell. (Users can put the oddest things in cells). Specificaly to guard against an error value in the cell.

The Val function takes a string and returns a number, it converts the strings returned via Split, into numbers to be used by the RGB function. It also protects against bad data, since Val("abc") returns 0 (cf. CDbl("abc") which errors).

Adding the "(0,0,0" at the start in CStr(Range("A1").Value) & "(0,0,0" is also there to protect against bad data in the cell. (Users are like Shakespeare's monkeys, you never know what keys they are going to press.)