Search code examples
ms-accessvbams-access-2016

Writing a function to update labels OnClick Access VBA

I'm creating a form that allows the user to select any number of Sigs so I can autopopulate a recordset with values. I recreated an option group of check boxes because management/supervisors have said the default check boxes are too small.

My Sig FormI have this code, which works, but I want to know if there is a way I can create a more generic function that I can reuse on each OnClick event instead of pasting the same code with minor alterations 36 times.

Private Sub lblChkSig1_Click()
    mblnArray(0) = Not (mblnArray(0))
    If mblnArray(0) Then
        lblChkSig1.Caption = Chr(80)    'check mark
        lblChkSig1.BackColor = RGB(0, 128, 0)     'Dark Green
    Else
        lblChkSig1.Caption = ""
        lblChkSig1.BackColor = RGB(255, 255, 255)    'White
    End If
End Sub

The array mblnArray is just an array of 36 boolean values that I use to so I can iterate through them all quickly. I have done this before, but only with as many as 4 values where redoing all the code wasn't an issue. Now that I have a larger set of options, I decided I'd like to change it to make it reusable.

I came up with this function as a replacement, but I can't figure out how to dynamically change which label is updated.

Private Sub UpdateChecks(iPos As Integer)
    Dim ctlCurrentControl as Label
    Set cltCurrentControl = Me.ActiveControl

    mblnArray(iPos) = Not (mblnArray(iPos))
    If mblnArray(iPos) Then
        ctlCurrentControl.Caption = Chr(80)    'check mark
        ctlCurrentControl.BackColor = RGB(0, 128, 0)     'Green
    Else
        ctlCurrentControl.Caption = ""
        ctlCurrentControl.BackColor = RGB(255, 255, 255)    'White
    End If

End Sub

I found out while looking for solutions that labels can't be the form's Active Control. I also tried

Dim ctlCurrentControl as Label
Set ctlCurrentControl = "lblChkSig" & iPos

but that did not work either. I don't know where to proceed from here. I am not certain this can even be done, but it would be very helpful if there is a solution.

Solution

  • You could use toggle buttons, they can even have an associated label that, when clicked, toggles the button. You could use a white X as the caption for all the toggle buttons, let the them appear with white forecolors (so you won't see the white X) and dark green color when pressed. Just play with the colors until they fit. This way, you won't need any code to toggle the color or caption.

    As an example, I used

    • Caption: X
    • BackColor: #FFFFFF
    • BorderStyle: Solid
    • BorderWidth: Hairline
    • BorderColor: #000000
    • HoverColor: #008000
    • PressedColor: #008000
    • HoverForeColor: #008000
    • PressedForeColor: #FFFFFF
    • TextColor: #FFFFFF

    Screenshot:

    Example