Streamlining Event Procedures RGBColor Wizard ~ LEARN MS-ACCESS TIPS AND TRICKS

RGB Color Wizard.

Create your own RGB Color Palette for Form Design.

This is a special episode focusing on streamlining the Form Module Code. For our RGB Color Wizard, we utilize an ActiveX ScrollBar Control. It's important to note that you cannot instantiate ActiveX controls such as Scrollbars, Sliders, TreeView, and ListView Controls in the standalone class module. Therefore, all event procedures for these controls must be written in the Form Module exclusively.

We employ three ScrollBar Controls dedicated to Red, Green, and Blue color code values. Each control spans from 0 to 255, representing the RGB color settings. Collectively, these values are utilized to generate a wide range of colors in the Color Wizard, logically providing the capability to create up to 16.7 million colors.

The RGB Color Wizard Image is given below.

Let us take a look at the User Interface of the Color Wizard.

The Wizard Controls.

To the left of the scrollbars, three textboxes are positioned. When you adjust the scrollbar slider to the right or left, the selected color number will be displayed in the textbox on the left side. The color range spans from 0 to 255 for red, green, and blue colors. Additionally, you have the option to manually input color numbers for a specific RGB color by typing them into the text boxes.

To the right of the scroll bars, three label controls display the intensity of the selected color number as you move the scrollbar, resembling a graph chart for Red, Green, and Blue. The RGB() function blends all three selected colors together, resulting in the generation of the RGB color. This final RGB color is prominently displayed in the large rectangle control below.

Once you are ready to save the generated new Color, click on the RGB Color rectangle Control to select the Color.

Next, Click on one of the 25 Color Boxes to save the selected Color. The existing Color in the color box will be replaced with the new one. The RGB Color Number is displayed in the RGBColor TextBox Control below the Colorbox Grid.

You can store and preserve up to 25 colors at any given time. When you wish to apply one of the saved colors to a control, such as a TextBox or Label Control, for properties like ForeColor, BackColor, or BorderColor, simply highlight the number in the RGB Color TextBox and copy-paste the color number into the desired color attribute property.

To copy the RGB Number to the Clipboard, Click on the Label, with the Caption 'Copy to Clipboard' below the RGB Color TextBox and paste the RGB Color number where you want it.

The Form Module Code with the ActiveX Control's OnChange() Event Procedures is given below.

Option Compare Database
Option Explicit

Private CWiz As CWiz_ObjInit 'Intermediary Class

Const GraphFactor = (1 / 255) * 1440 'The Color Graph Width is 1 inch
Dim intR As Long, intG As Long, intB As Long
Dim cdb As Database, doc As Document

Private Sub Form_Load()

Set CWiz = New CWiz_ObjInit 'Instantiate CWiz_ObjInit Class
Set CWiz.o_Frm = Me 'Assign the Form object to its Property

End Sub

Private Sub form_Unload(Cancel As Integer)
    Set CWiz = Nothing
End Sub

'ActiveX Control
Private Sub Ctl_B_Change() 'Blue Color ScrollBar Control
Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")

intB = Ctl_B.Value
With Me
    ![BN] = intB
    .B.Width = GraphFactor * intB
    .B.BackColor = RGB(0, 0, intB)
    .Color.BackColor = RGB(intR, intG, intB)
    .RGBColor = .Color.BackColor

'Save RGBColor and BN TextBoxes contents in Form Custom Properties
doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("BN").Value = intB

    .Controls("Color").SpecialEffect = 0
    .CheckBox.Value = False
End With
End Sub

'ActiveX Control
Private Sub Ctl_G_Change()
Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")

intG = Me.Ctl_G.Value

With Me
    intG = .Ctl_G.Value
    ![GN] = intG
    .G.Width = GraphFactor * intG
    .G.BackColor = RGB(0, intG, 0)
    .Color.BackColor = RGB(intR, intG, intB)
    .RGBColor = Color.BackColor

doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("GN").Value = intG

    .Controls("Color").SpecialEffect = 0
    .CheckBox.Value = False
End With

End Sub

'ActiveX Control
Private Sub Ctl_R_Change()
Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")

intR = Me.Ctl_R.Value
With Me
    ![RN] = intR
    .R.Width = GraphFactor * intR
    .R.BackColor = RGB(intR, 0, 0)
    .Color.BackColor = RGB(intR, intG, intB)
    .RGBColor = Color.BackColor

doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("RN").Value = intR

    .Controls("Color").SpecialEffect = 0
    .CheckBox.Value = False
End With
End Sub

The ScrollBar Controls.

The three ScrollBars, named Ctl_R, Ctl_G, and Ctl_B, correspond to the Red, Green, and Blue colors. When the slider control on each is moved to adjust the color numbers within the range of 0 to 255, the Change Event is triggered. This event records these actions, updating other related controls, such as the TextBox content on the left side. Additionally, it increases the width of the label controls, dynamically displaying color variations based on the selected color number range.

In addition, the Change Event updates the RGB color displayed in the large rectangular Label Control and the RGB color number in the TextBox located below the Colors Grid.

Since we cannot create an instance of the ScrollBar ActiveX Control in the standalone Class Module we are forced to write the Change Event Subroutines in the Form's Class Module.

The ColorWizard and Run-Time Data.

Normally, we make changes to a control's ForeColor, BackColor, and Border Colors in the Design View of the Form, update the values in those Properties, and save them when the Form is closed. When we open the Form again it will display the changes as we made in the design view.

However, with this RGB Color Wizard, we are modifying control properties in the normal Form View Mode. Any alterations made in this mode are temporary and are not automatically saved, and they will be lost when the form is closed. While a logical solution might be to save all settings in a table, allowing them to be loaded the next time the form is opened.

Contrary to the conventional approach of using tables for everything, we're opting for a different method. In this case, we'll save the entire data within the form itself. While not a novel concept, this method is rarely employed due to its complexity. Specifically, we'll store the data in the form's custom-made properties, akin to the Tag property of a form or control. Creating these properties with VBA programs is possible, although the route taken for this process is somewhat uncommon.

You can see how these Custom Properties are addressed for storing/retrieving data in them. For an introduction to this method visit this Link: Saving Data on Forms, Not in Table to see a simple practical use of this method.

To preserve data from the ColorGrid, Text Boxes, and other controls during changes or when closing, we implemented custom properties to store this information.

The saving of values to all custom properties occurs when the form is closed. Upon reopening the form, these values are read from the custom properties and displayed on the corresponding controls. These two event procedures are implemented in the CWiz_ObjInit Class Module.

Regarding the ScrollBar Change Event Subroutine, pay attention to the subsequent lines responsible for updating custom properties and the method employed to address and store values into them:

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("ColorPalette")
.
.
.
doc.Properties("RGBColor").Value = .Color.BackColor
doc.Properties("BN").Value = intB
.

Before saving the values into the Custom Properties we must create the Properties on the Form. This is a one-time exercise.

Sample Custom Property Management VBA Code.

Let us see an example of creating a Custom Property to save an Employee's Name in Form1. Sample VBA Code is given below:

'Create a Custom Property in Form1
Private Sub CreateProperty()
Dim db As Database
Dim doc As Document
Dim prp As Property

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")

Set prp = doc.CreateProperty("EmpName", dbText, "SampleText")

doc.Properties.Append prp

Set prp = Nothing
Set doc = Nothing
Set db = Nothing

End Sub

'Assign a value to Custom Property in Form1
Private Sub AssignPropertyValue()

Dim db As Database
Dim doc As Document

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")
    doc.Properties("EmpName").Value = "Michael Colins"

Set doc = Nothing
Set db = Nothing

End Sub

'Create a Custom Property in Form1
Private Sub ReadPropertyValue()

Dim db As Database
Dim doc As Document
Dim strName As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")
  strName = doc.Properties("EmpName").Value
  MsgBox "Name: " & UCase(strName)

Set doc = Nothing
Set db = Nothing

End Sub

'Create a Custom Property in Form1
Private Sub DeleteProperty()

Dim db As Database
Dim doc As Document

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Form1")
    doc.Properties.Delete "EmpName"
    
Set doc = Nothing
Set db = Nothing
End Sub

All the procedures for creating a custom property to save an employee's name, assigning a name to the property, reading it back, displaying it in a message box, and deleting the custom property from Form1 are outlined in the individual subroutines above.

The CWiz_TextBox Wrapper Class.

The CWiz_TextBox Wrapper Class manages the AfterUpdate() Event, allowing for direct entry of color numbers for Red, Green, and Blue into the TextBoxes named RN, GN, and BN. The subsequent changes are seamlessly reflected in the ScrollBars, the color graphs situated to the right of the ScrollBars, the new color showcased in the large rectangle label background, and the RGB color number displayed in the TextBox.

You may save your new Color in the Color Grid as explained earlier.

The CWiz_TextBox Wrapper Class Module Code is given below:

Option Compare Database
Option Explicit

Private WithEvents ctxt As Access.TextBox
Private cFrm As Form

Const GraphFactor = (1 / 255) * 1440
Private db As Database
Private doc As Document

Public Property Get c_Frm() As Form
    Set c_Frm = cFrm
End Property

Public Property Set c_Frm(ByRef vcFrm As Form)
    Set cFrm = vcFrm
End Property

Public Property Get c_txt() As Access.TextBox
    Set c_txt = ctxt
End Property

Public Property Set c_txt(ByRef vctxt As Access.TextBox)
    Set ctxt = vctxt
    
    Set db = CurrentDb
    Set doc = db.Containers("Forms").Documents("ColorPalette")
End Property

Private Sub ctxt_AfterUpdate()
 With cFrm
    Select Case ctxt.Name
        Case "RN"
            .Ctl_R.Value = cFrm![RN]
            doc.Properties("RN").Value = cFrm!RN
            .CheckBox.Value = False
        Case "GN"
            .Ctl_G.Value = cFrm![GN]
            doc.Properties("GN").Value = cFrm!GN
            .CheckBox.Value = False
        Case "BN"
            .Ctl_B.Value = cFrm![BN]
            doc.Properties("BN").Value = cFrm!BN
            .CheckBox.Value = False
    End Select
  End With
End Sub

The CWiz_Label Wrapper Class.

The Labels in the ColorGrid, the Color Graph Labels to the right side of the ScrollBars, and the RGB Color Display Label and all their Click Event Subroutines are kept in the CWiz_Label Wrapper Class.

The CWiz_Label Wrapper Class Module Event Procedure Code is given below:

Option Compare Database
Option Explicit

Private WithEvents clbl As Access.Label
Private sFrm As Form

Const GraphFactor = (1 / 255) * 1440
Private db As Database
Private doc As Document

Private selflag As Boolean
Private lngColor As Long

Public Property Get s_Frm() As Form
    Set s_Frm = sFrm
End Property

Public Property Set s_Frm(ByRef vsFrm As Form)
    Set sFrm = vsFrm
End Property

Public Property Get s_clbl() As Access.Label
    Set s_clbl = clbl
End Property

Public Property Set s_clbl(ByRef vclbl As Access.Label)
    Set clbl = vclbl
    
    Set db = CurrentDb
    Set doc = db.Containers("Forms").Documents("ColorPalette")
End Property

Private Sub clbl_Click()
Dim I As Integer
If Val(Mid(clbl.Name, 5)) > 0 Then
    I = Val(Mid(clbl.Name, 5))
End If
Select Case I
    Case 1 To 25
        Call Boxes(I) 'Click on Color Grid
End Select

Select Case clbl.Name
    Case "Color"
        Call ColorClick 'Click on the RGB Color Display Label
    Case "Clip"
       Call ClipClick   'Click on this Labek to Copy RGB Color number to ClipBoard
End Select
End Sub

Private Sub ColorClick()
With sFrm
    lngColor = .Color.BackColor
    !RGBColor = .Controls("Color").BackColor
    .Controls("Color").SpecialEffect = 2
    
    'Copy the created color to the grid
    !CheckBox.Value = True
End With
End Sub

Private Sub ClipClick()
    If Not IsNull(sFrm![RGBColor]) Then
        ' Copy the TextBox contents to the clipboard
        sFrm.RGBColor.SetFocus
        DoCmd.RunCommand acCmdCopy
        MsgBox "RGB Color Number Copied to Clipboard!", vbInformation
    Else
        ' Display a message if the TextBox is empty
        MsgBox "RGBColor is empty!", vbExclamation
    End If

End Sub

Private Sub Boxes(ByVal bx As Integer)
Dim j As Integer
Dim ctl As String
Dim Colr As Long
Dim intR As Integer
Dim intG As Integer
Dim intB As Integer

selflag = sFrm!CheckBox.Value

For j = 1 To 25
If j = bx Then
   If selflag Then
    With sFrm
      ctl = "lblC" & j
        .Controls(ctl).SpecialEffect = 2
        .Controls(ctl).BackColor = .Color.BackColor
        doc.Properties("Selected").Value = .Controls(ctl).BackColor
        !RGBColor = .Controls(ctl).BackColor
        !CheckBox.Value = False
      ctl = "C" & j
      doc.Properties(ctl).Value = .Color.BackColor
      doc.Properties("Selctl").Value = "C" & j
    End With
   Else
    With sFrm
         ctl = "lblC" & j
            !RGBColor = .Controls(ctl).BackColor
            .Controls(ctl).SpecialEffect = 2
         doc.Properties("Selected").Value = .Controls(ctl).BackColor
         doc.Properties("Selctl").Value = "C" & j
   End With
   
 Colr = sFrm!RGBColor
 'Split into R,G,B
 intR = Colr Mod 256
 intG = Colr \ 256 Mod 256
 intB = Colr \ 256 \ 256 Mod 256
 
 With sFrm
    !RN = intR
    .Ctl_R.Value = sFrm!RN
    !GN = intG
    .Ctl_G.Value = sFrm!GN
    !BN = intB
    .Ctl_B.Value = sFrm!BN
 
    .R.Width = GraphFactor * intR
    .G.Width = GraphFactor * intG
    .B.Width = GraphFactor * intB

    .R.BackColor = RGB(intR, 0, 0)
    .G.BackColor = RGB(0, intG, 0)
    .B.BackColor = RGB(0, 0, intB)

    .Color.BackColor = RGB(intR, intG, intB)
  End With
  With doc
    .Properties("RGBColor").Value = sFrm.Color.BackColor
    .Properties("RN").Value = intR
    .Properties("GN").Value = intG
    .Properties("BN").Value = intB
  End With
  End If
   
Else
   ctl = "lblC" & j
   sFrm.Controls(ctl).SpecialEffect = 0
End If

Next

End Sub

The Intermediary Class Module CWiz_ObjInit VBA Code is given below:

Option Compare Database
Option Explicit

Private cw As CWiz_Label
Private txt As CWiz_TextBox
Private WithEvents cmd As CommandButton

Private WithEvents frm As Form
Private coll As New Collection

Const GraphFactor = (1 / 255) * 1440
Const MaxColor = 25
Private cdb As Database, ctr As Container, doc As Document


Public Property Get o_Frm() As Form
    Set o_Frm = frm
End Property

Public Property Set o_Frm(ByRef voFrm As Form)
    Set frm = voFrm
    
    Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"
Dim I As Integer

Call ColorPalette_Init 'Initialize

Set cmd = frm.cmdClose
    cmd.OnClick = EP
     
For Each ctl In frm.Controls

 I = Val(Mid(ctl.Name, 5))
 
  Select Case TypeName(ctl)
    Case "Label"
      Select Case I
          Case 1 To 25
            Set cw = New CWiz_Label
            Set cw.s_Frm = frm
            Set cw.s_clbl = ctl
                cw.s_clbl.OnClick = EP
            coll.Add cw
            Set cw = Nothing
      End Select
      Select Case ctl.Name
        Case "Color"
            Set cw = New CWiz_Label
            Set cw.s_Frm = frm
            Set cw.s_clbl = ctl
                cw.s_clbl.OnClick = EP
                
            coll.Add cw
            Set cw = Nothing
        Case "Clip"
            Set cw = New CWiz_Label
            Set cw.s_Frm = frm
            Set cw.s_clbl = ctl
                cw.s_clbl.OnClick = EP
            coll.Add cw
            Set cw = Nothing
        End Select
     
      Case "TextBox"
        Select Case ctl.Name
            Case "RN", "GN", "BN"
              Set txt = New CWiz_TextBox
              Set txt.c_Frm = frm
              Set txt.c_txt = ctl
                txt.c_txt.AfterUpdate = EP
                coll.Add txt
              Set txt = Nothing
            Case "RGBColor"
              Set txt = New CWiz_TextBox
              Set txt.c_Frm = frm
              Set txt.c_txt = ctl
                txt.c_txt.OnGotFocus = EP
                coll.Add txt
              Set txt = Nothing
        End Select
    End Select
Next
End Sub

Private Sub ColorPalette_Init()
Dim xRN As Integer
Dim xGN As Integer
Dim xBN As Integer
Dim xRGBColor As Long
Dim j As Integer
Dim cdb As Database
Dim ctr As Container
Dim doc As Document
Dim strctl As String

Set cdb = CurrentDb
Set ctr = cdb.Containers("Forms")
Set doc = ctr.Documents("ColorPalette")

xRN = doc.Properties("RN").Value
xGN = doc.Properties("GN").Value
xBN = doc.Properties("BN").Value
xRGBColor = doc.Properties("RGBColor").Value

With frm 
    ![RN] = xRN
    ![GN] = xGN
    ![BN] = xBN
    .R.Width = xRN * GraphFactor
    .R.BackColor = RGB(xRN, 0, 0)
    
    .G.Width = xGN * GraphFactor
    .G.BackColor = RGB(0, xGN, 0)
    
    .B.Width = xBN * GraphFactor
    .B.BackColor = RGB(0, 0, xBN)
    
.Ctl_R.Value = xRN
.Ctl_G.Value = xGN
.Ctl_B.Value = xBN

.Color.BackColor = RGB(xRN, xGN, xBN)
.RGBColor = .Color.BackColor
End With

For j = 1 To MaxColor
   strctl = "lblC" & j
   frm.Controls(strctl).BackColor = doc.Properties("C" & j).Value
   If ("C" & j) = doc.Properties("Selctl").Value Then
      frm.Controls(strctl).SpecialEffect = 2
   End If

Next j

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err.Description, , "Form_Load"
Resume Form_Load_Exit

End Sub

Private Sub cmd_Click()
Dim msg As String
Dim ctl As String, strC1 As String, j As Integer

msg = "Close the Color Wizard?"
If MsgBox(msg, vbYesNo + vbQuestion, "cmd_Click()") = vbYes Then

    Set cdb = CurrentDb
    Set ctr = cdb.Containers("Forms")
    Set doc = ctr.Documents("ColorPalette")

For j = 1 To MaxColor
  ctl = "lblC" & j
  strC1 = "C" & j
    doc.Properties(strC1).Value = frm.Controls(ctl).BackColor
  If frm.Controls(ctl).SpecialEffect = 2 Then
      doc.Properties("Selected").Value = frm.Controls(ctl).BackColor
      doc.Properties("SelCtl").Value = strC1
  End If
Next
    doc.Properties("RGBColor").Value = Nz(frm.Controls("RGBColor").Value, 0)
    DoCmd.Close acForm, frm.Name
End If
End Sub

Private Sub Class_Terminate()
Do While coll.Count > 1
    coll.Remove 1
Loop
End Sub

When the form is opened, the form object is passed to the intermediary class module, initiating the execution of the Class_Init subroutine. The first subroutine, ColorPalette_Init, is invoked from within the Class_Init subroutine. This procedure retrieves all the values saved in the form's custom properties and assigns them to the labels, scroll bars, and text boxes on the form.

This procedure is normally run in the Form_Load() Event Procedure and the current Values on the Form Controls are saved when the Form is Closed.

There is a single command button to close the form. A singular command button object instance is created in the Intermediary Class Module, and its Click Event is enabled. Consequently, when the cmdClose command button is clicked, the form close event procedure is executed in the CWiz_ObjInit Module. Before closing the form, all the values of the Color Wizard form controls are saved in the form's custom properties.

This topic was initially published in October 2010, featuring a color palette of 15 colors, and all the wizard VBA code was implemented in the Form Module. The older version of the wizard form is included in the demo database, labeled ColorPaletteOld. Feel free to open and review the code, assessing how it has been transformed into a form that can now be executed from the standalone class module, excluding the VBA code related to the ActiveX Control ScrollBar.