Why my checkboxes doesn't work when I try to launch my userform with a button on the sheet and work when I launch the userform from the main menu?

Question

I'm currently working on a small project in VBA, therefore I have created an UserForm with two checkboxes and I wrote 2 subs proper to these checkboxes to make sure that only one of the checkboxes can be checked. Here is the code of the 2 checkboxes :

Private Sub CheckBox2_Click()
    If CheckBox2.Value = True And CheckBox3.Value = True Then
        CheckBox3.Value = False
        TextBox8.Enabled = True
        TextBox8.BackColor = RGB(255, 255, 255)
    Else
        CheckBox3.Value = False
        CheckBox2.Value = True    
    End If
End Sub

Private Sub CheckBox3_Click()
    If CheckBox3.Value = True And CheckBox2.Value = True Then
        CheckBox2.Value = False
        TextBox8.Enabled = False
        TextBox8.BackColor = RGB(205, 205, 205)
    Else
        CheckBox2.Value = False
        CheckBox3.Value = True
        TextBox8.Enabled = False
    End If
End Sub

As you have seen, the subs also enable/disables a TextBox field considering the checkbox checked.

This code perfectly works when I try to launch the Userform from the main menu with the Run button : il you check a checkbox, the other one will be unchecked.

I also create on the first sheet of my Excel file, a button that launches the userform, so the user won't have to activate the developer's menu to launch it. Here is the sub attached to this button :

Private Sub lance_interface()
    UF.Show
End Sub

When I click on this button, the userform appears but once a checkbox is checked, there's no way to check the other checkbox in order to uncheck the first one : the first one keeps being checked.

I hope that my problem is clearly explained, thanks in advance for your help and sorry for my english mstakes.

Storax Storax · Accepted Answer · 2020-03-20T18:39:25

You need to disable events in your userform. In your case that might look like that

Option Explicit
Dim eventOn As Boolean
Private Sub CheckBox2_Click()
    If eventOn Then
        eventOn = False
        If CheckBox2.Value = True And CheckBox3.Value = True Then
            CheckBox3.Value = False
            TextBox8.Enabled = True
            TextBox8.BackColor = RGB(255, 255, 255)
        Else
            CheckBox3.Value = False
            CheckBox2.Value = True
        End If
        eventOn = True
    End If
End Sub

Private Sub CheckBox3_Click()
    If eventOn Then
        eventOn = False
        If CheckBox3.Value = True And CheckBox2.Value = True Then
            CheckBox2.Value = False
            TextBox8.Enabled = False
            TextBox8.BackColor = RGB(205, 205, 205)
        Else
            CheckBox2.Value = False
            CheckBox3.Value = True
            TextBox8.Enabled = False
        End If
        eventOn = True
    End If

End Sub

Private Sub UserForm_Initialize()
    eventOn = True
End Sub

Fo further reading look at ChipPearson article on Surpress Change in Forms or disabling-events-in-userforms

Why my checkboxes doesn't work when I try to launch my userform with a button on the sheet and work when I launch the userform from the main menu?

1 Answers