Using VBA to create multiple color scheme options for worksheets

[jmwbowen]

I have a worksheet containing rectangle shapes being used at buttons. Clicking the shapes points to different macros that change data on another sheet. The macro also changes the color of the shape to indicate that it has been clicked "ON". Clicking the shape again would again change the data on another sheet and color the shape "OFF".

The Shapes Title property is used to reference a named range on the other sheet.

Here's some sample code:

Set MyShape = ActiveSheet.Shapes(Application.Caller)With MyShape
    Set DataCell = DetectionData.Range(.Title).Offset(DetectionData.Range("DT_RecordNumber").Value, 0)
    
    With .Fill.ForeColor
    If .RGB = RGB(150, 200, 200) Then
        .RGB = RGB(75, 150, 150)
        DataCell.Value = True
    Else
        .RGB = RGB(150, 200, 200)
        DataCell.Value = False
    End If
    End With
End With
Set MyShape = Nothing
Set DataCell = Nothing

The RGB colors are a light and dark blue. However, I'd like to offer the user the option to change the color scheme of the buttons. This would involve 3 different RGB codes, one for the "ON" status, one for the "OFF" status, and one for the Shape border color.

My idea is that a pop-up userform would handle changing the color scheme. Is there some way to create a variable or object or class object (I don't know anything about classes) that would hold the RGB values and then the above could would look something like the following below?

Set MyShape = ActiveSheet.Shapes(Application.Caller)With MyShape
    Set DataCell = DetectionData.Range(.Title).Offset(DetectionData.Range("DT_RecordNumber").Value, 0)
    
    With .Fill.ForeColor
    If .RGB = ButtonColorOn Then
        .RGB = ButtonColorOff
        DataCell.Value = True
    Else
        .RGB = ButtonColorOn
        DataCell.Value = False
    End If
    End With
End With
Set MyShape = Nothing
Set DataCell = Nothing

The ButtonColorOn and ButtonColorOff objects (variables? class objects?) would hold the RGB values and the userform that changes color schemes would simply change the values inside these objects.

 

[jmwbowen]

OP EDIT:

Another thought (again, I don't know much about classes, YET) is creating a class? made up of a Shape object with specific properties working with the RGB values so that code would look something like the following:

If MyButton.Status = StatusOn Then
     MyButton.Status = StatusOff
     DataCell = False
Else
     MyButton.Status = StatusOn
     DataCell = True
End If

Again, I just don't know how much can be done with RGB values.