CODEWITHSUNDEEP
excelvba
SelpaqM
SelpaqM

Reputation: 63

CheckBox triggered another checkbox excel macro

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.

Upvotes: 1

Views: 1226

Answers (1)

PeterT
PeterT

Reputation: 8557

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

Upvotes: 2

Related Questions