Referencing RGB Value not as String

[vg1701]

I am creating many userforms for a project and I want to have a common color theme. And I created a column that has all the colors I want to use.

So instead of having to say Label1.ForeColor= RGB(0,0,0) in each userform, I want to be able to just reference that cell that has the color information. So if the color scheme changes and I want to change RGB(0,0,0) to RGB(23, 100,200), I can just change it in one place instead of having to change the label colors in all the userforms.


I additionally created a named range for each cell in the the column KT, because I thought I could reference named ranges easily to get the values. Here is my current code:

Label1.ForeColor= Sheet1.Range("HeaderColor").value 'In this case, HeaderColor references the named range I created just for that one cell in the 6th row of column KT

However, it throws a TypeMismatch error because it sets Label1.ForeColor = "RGB(0,112,255)" and I think it reads it as a string. How do I get it to remove the quotes? And have Sheet1.Range("HeaderColor").value not return a string, or somehow have it to be a compatible type?

Please help, TIA

 

[lrobbo314]

Instead of storing them in the worksheet, why not just set them up as variables in your code? You would still only need to change it in 1 place.

Something like this?

Public header_color As Long
Public highlight_text_1 As Long

Private Sub UserForm_Initialize()
header_color = RGB(0, 112, 255)
highlight_text_1 = RGB(51, 255, 189)
End Sub

Private Sub CommandButton1_Click()
Me.Label1.ForeColor = header_color
Me.Label2.ForeColor = highlight_text_1
End Sub

 

[vg1701]

Instead of storing them in the worksheet, why not just set them up as variables in your code? You would still only need to change it in 1 place.

Something like this?

Public header_color As Long
Public highlight_text_1 As Long

Private Sub UserForm_Initialize()
header_color = RGB(0, 112, 255)
highlight_text_1 = RGB(51, 255, 189)
End Sub

Private Sub CommandButton1_Click()
Me.Label1.ForeColor = header_color
Me.Label2.ForeColor = highlight_text_1
End Sub

Although this would work, I need to store the color information on the worksheet. All the information regarding the visual aspect is kept on the worksheet and I'd like to keep it there.

 

[Akuini]

@vg1701
Welcome to MrExcel.
Maybe this:
On the sheet, set the cell's interior color as needed then use it to set the label color.
Something like this:

Book1
	A	B
1
2
3
Sheet1

Label1.ForeColor = Sheets("Sheet1").Range("A1").Interior.Color

 

[Akuini]

Another option:
RGB is a function, it returns a Long whole number representing an RGB color value.
So put the number in the cell instead of for example "RGB(10, 130, 255)".
Here's how:
This will put 16744970 in A1:

Sub toLongRGB()
Range("A1") = RGB(10, 130, 255)
End Sub

then you can use it directly:

Label1.ForeColor = Sheets("Sheet1").Range("A1").Value

 

[dmt32]

Hi,
Another approach to consider would be to create a group of Enumerated constants listing your Color Names with their RGB long values. Placed in a standard module, all constants can then be accessed across your application. It is easier to use constant names rather than trying to remember the the correct numeric value and this approach also has the advantage of displaying the IntelliSense showing the member list you can select from.

If you need to update your system colors across your application – you just amend the Enumeration member values.

STANDARD Module

Public Enum FRM_SystemColors
    frm_highlight_text_1 = 16740352 'RGB(0, 112, 255)
    frm_highlight_text_2 = 12451635 'RGB(51, 255, 189)
    frm_BackColor = 16777215        'VBWhite
    frm_header_color = 16740352     'RGB(0, 112, 255)
    frm_LabelColor = 0              'VBBlack
End Enum

In use

Private Sub CommandButton1_Click()
    Me.Label1.ForeColor = FRM_SystemColors.frm_header_color
    Me.Label2.ForeColor = FRM_SystemColors.frm_highlight_text_1
End Sub

to get the long value of the RGB value to Enum, enter into the Immediate window

? RGB(51, 255, 189)

12451635

Hope Helpful

Dave