CODEWITHSUNDEEP
vbaexcelcheckbox
Jakob Busk Sørensen
Jakob Busk Sørensen

Reputation: 6081

Skip check box Click() function [VBA]

I have user form, which I use to show the status for an item. The items go through a 3 stage process. For each process there is a check box, which can be checked once the item has been through a stage. When clicked, the user is presented with an "Are you sure?" prompt. If the user selects "Yes" (vbYes) the process is marked as completed, in an SQL table.

The issue arises when I want to show items which have already been through one or more stages. When the form is initialized, I set the value of the check boxes for the relevant stages to "True". However, this activates the click function of the check box, which is not intended. Is there a way to skip this?

Code example (not actual code)

Private Sub UserForm_Initialize()
    'Check check boxes, where stages are completed
    If stage_1_completed Then
        chkStage1.value = True
    End If
    If stage_2_completed Then
        chkStage2.value = True
    End If
    If stage_3_completed Then
        chkStage1.value = True
    End If

End Sub

'This part should only run, if the check box is clicked manually
Private Sub chkStage1_Click()
    If chkStage1.value = True Then
        'Prompt user if sure?
        promptAnswer = MsgBox("Are you sure?", vbYesNo, "Continue?")

        'Mark stage 1 as processed if yes
        If promptAnswer = vbYes Then
            set_stage_completed (1)
        End If

    End If
End Sub

Upvotes: 2

Views: 407

Answers (1)

Florent B.
Florent B.

Reputation: 42518

One way is to use a variable to store the state:

Public EnableEvents As Boolean

Private Sub UserForm_Initialize()
    Me.EnableEvents = False

    ''
    ' initialize form here
    ''

    Me.EnableEvents = True
End Sub

Private Sub chkStage1_Click()
    If Not Me.EnableEvents Then Exit Sub

    ''
    ' handle event here
    ''
End Sub

Upvotes: 1

Related Questions