CheckBox triggered another checkbox excel macro

Question

I have 3 check boxes. no1 is the main checkbox. when it is checked no2 and no3 is enabled and when no1 is unchecked, no2 and no3 is disabled. when no2 is checked, no3 is disabled, and same for no3. on below code, everything is working nearly perfect, unless if the no3 is checked and then no1 is unchecked, no2 is enabled. normally it should be disabled due to no1 is unchecked.

Private Sub CheckBox1_Click()
Application.EnableEvents = False
If CheckBox1.Value = True Then
    CheckBox2.Enabled = True
    CheckBox3.Enabled = True
Else
    CheckBox2.Enabled = False
    CheckBox2.Value = False
    CheckBox3.Enabled = False
    CheckBox3.Value = False
End If
Application.EnableEvents = True
End Sub

Private Sub CheckBox2_Click()
Application.EnableEvents = False
If CheckBox2.Value = True Then
Sheets("Deep Storage Box").Visible = True
    CheckBox3.Enabled = False
    CheckBox3.Value = False
Else
Sheets("Deep Storage Box").Visible = False
CheckBox3.Enabled = True
End If
Application.EnableEvents = True
End Sub

Private Sub CheckBox3_Click()
Application.EnableEvents = False
If CheckBox3.Value = True Then
Sheets("Deep Storage Hanging").Visible = True
    CheckBox2.Enabled = False
    CheckBox2.Value = False
Else
Sheets("Deep Storage Hanging").Visible = False
CheckBox2.Enabled = True
End If
Application.EnableEvents = True
End Sub

I think when I unchecked the no1, "Private Sub CheckBox3_Click()" after "Else" section is trggering.

PeterT · Accepted Answer · 2020-05-07 22:15:55Z

2

The main problem you run into when trying to handle a multi-state user interface is how to account for all the different possible states of the user controls (in your case, there are three checkboxes). You'll tie yourself in knots trying to work through the permutations if you design logic based on individual checkbox events.

My approach has always been to extract the user experience logic in a single routine. This way, you're always proceeding logically to determine the state of your controls (checkboxes) and how to set the user controls accordingly.

(I only think that I understand your control logic from your description. Please modify this example below to fit your exact needs.)

In its own code module, I most often create an easy method to toggle the state of events and displays:

Option Explicit

Public Sub ToggleAppSettings(ByVal newState As Boolean)
    With Application
        .EnableEvents = newState
        .ScreenUpdating = newState
    End With
End Sub

Then, in the code module for the worksheet containing the checkboxes:

Option Explicit

Private Sub CheckBox1_Click()
    SetUXState
End Sub

Private Sub CheckBox2_Click()
    SetUXState
End Sub

Private Sub CheckBox3_Click()
    SetUXState
End Sub

Public Sub SetUXState()
    '--- review the current checkbox status to determine -- and set --
    '    the visibility and usability state of user-exposed controls
    ToggleAppSettings False
    If CheckBox1.Value = False Then
        '--- this is the simplest state, all other controls are disabled
        '    and worksheets are hidden
        CheckBox2.Value = False
        CheckBox2.Enabled = False
        CheckBox3.Value = False
        CheckBox3.Enabled = False
        With ThisWorkbook
            .Sheets("Deep Storage Box").Visible = False
            .Sheets("Deep Storage Hanging").Visible = False
        End With
    Else
        CheckBox2.Enabled = True
        CheckBox3.Enabled = True
        If CheckBox2.Value = True Then
            ThisWorkbook.Sheets("Deep Storage Box").Visible = True
            ThisWorkbook.Sheets("Deep Storage Hanging").Visible = False
            CheckBox3.Value = False
            CheckBox3.Enabled = False
        ElseIf CheckBox3.Value = True Then
            ThisWorkbook.Sheets("Deep Storage Box").Visible = False
            ThisWorkbook.Sheets("Deep Storage Hanging").Visible = True
            CheckBox2.Value = False
            CheckBox2.Enabled = False
        Else
            ThisWorkbook.Sheets("Deep Storage Box").Visible = False
            ThisWorkbook.Sheets("Deep Storage Hanging").Visible = False
        End If
    End If
    ToggleAppSettings True
End Sub

answered May 7, 2020 at 22:15

PeterT

8,5971 gold badge19 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brian M Stafford Over a year ago

This seems to solve the OP's problem. Any idea why setting the value for Checkbox3 in the CheckBox1_Click event fired the CheckBox3_Click event even though EnableEvents was False?

SelpaqM Over a year ago

without any changes fits perfectly and work smoothly. thanks @PeterT

SelpaqM Over a year ago

I have no clue about it but just one step is not working well. by the way @PeterT 's answer is working. I am not even checked it. :)

PeterT Over a year ago

@BrianMStafford - honestly I'm not sure. I may have run into a similar issue long ago (among other issues) and developed this pattern for working with user-interaction states. So I just skipped the OP issue and "solved" that part with my single-routine pattern.

Collectives™ on Stack Overflow

CheckBox triggered another checkbox excel macro

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related