VBA - Enable Checkboxes with conditions

[cydmm]

Hi guys,
I am working on a project where the user can decide, through some CheckBoxes and ComboBoxes placed on a Worksheet, to apply a multilevel sorting to a table placed on the same Sheet.
The code I wrote checks if each checkbox is selected and applies formatting rules in both cases. This code works out perfectly.

Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
If CheckBox1 Then
    Me.ComboBox1.Enabled = True
    Me.ComboBox1.BackColor = &HC0FFFF
    Me.ComboBox5.Enabled = True
    Me.ComboBox5.BackColor = &HC0FFFF
Else
    Me.ComboBox1.Enabled = False
    Me.ComboBox1.Value = ""
    Me.ComboBox1.BackColor = &HE0E0E0
    Me.ComboBox5.Enabled = False
    Me.ComboBox5.Value = ""
    Me.ComboBox5.BackColor = &HE0E0E0
End If
Application.ScreenUpdating = True
End Sub
Private Sub CheckBox2_Click()
Application.ScreenUpdating = False
If CheckBox2 Then
    Me.ComboBox2.Enabled = True
    Me.ComboBox2.BackColor = &HC0FFFF
    Me.ComboBox6.Enabled = True
    Me.ComboBox6.BackColor = &HC0FFFF
Else
    Me.ComboBox2.Enabled = False
    Me.ComboBox2.Value = ""
    Me.ComboBox2.BackColor = &HE0E0E0
    Me.ComboBox6.Enabled = False
    Me.ComboBox6.Value = ""
    Me.ComboBox6.BackColor = &HE0E0E0
End If
Application.ScreenUpdating = True
End Sub
Private Sub CheckBox3_Click()
Application.ScreenUpdating = False
If CheckBox3 Then
    Me.ComboBox3.Enabled = True
    Me.ComboBox3.BackColor = &HC0FFFF
    Me.ComboBox8.Enabled = True
    Me.ComboBox8.BackColor = &HC0FFFF
Else
    Me.ComboBox3.Enabled = False
    Me.ComboBox3.Value = ""
    Me.ComboBox3.BackColor = &HE0E0E0
    Me.ComboBox8.Enabled = False
    Me.ComboBox8.Value = ""
    Me.ComboBox8.BackColor = &HE0E0E0
End If
Application.ScreenUpdating = True
End Sub
Private Sub CheckBox4_Click()
Application.ScreenUpdating = False
If CheckBox4 Then
    Me.ComboBox4.Enabled = True
    Me.ComboBox4.BackColor = &HC0FFFF
    Me.ComboBox7.Enabled = True
    Me.ComboBox7.BackColor = &HC0FFFF
Else
    Me.ComboBox4.Enabled = False
    Me.ComboBox4.Value = ""
    Me.ComboBox4.BackColor = &HE0E0E0
    Me.ComboBox7.Enabled = False
    Me.ComboBox7.Value = ""
    Me.ComboBox7.BackColor = &HE0E0E0
End If
Application.ScreenUpdating = True
End Sub





[ATTACH type="full"]62751[/ATTACH]

What I would like to achieve is to let the user select each Checkbox from the Level 2 till the end only if all the previous ones are checked. In case they are not, a message should inform the user that he made a mistake.

I am not able to write an IF statement in VBA that checks more than one condition at the same time before ELSE instruction (I tried also with SELECT CASE...CASE but I have the same difficulty).

Would you please help me?

Thanks a lot

Mark

 

[DanteAmor]

Try the following:

Private Sub CheckBox1_Click()
  Application.ScreenUpdating = False
  If CheckBox1 Then
    Me.ComboBox1.Enabled = True
    Me.ComboBox1.BackColor = &HC0FFFF
    Me.ComboBox5.Enabled = True
    Me.ComboBox5.BackColor = &HC0FFFF
  Else
    Me.ComboBox1.Enabled = False
    Me.ComboBox1.Value = ""
    Me.ComboBox1.BackColor = &HE0E0E0
    Me.ComboBox5.Enabled = False
    Me.ComboBox5.Value = ""
    Me.ComboBox5.BackColor = &HE0E0E0
    
    Me.CheckBox2.Value = False
    Me.CheckBox3.Value = False
    Me.CheckBox4.Value = False
  End If
  Application.ScreenUpdating = True
End Sub

Private Sub CheckBox2_Click()
  Application.ScreenUpdating = False
  If CheckBox2 Then
    
    If Me.CheckBox1.Value = False Then
      MsgBox "Checkbox needs to be checked."
      CheckBox2.Value = False
      Exit Sub
    End If
    
    Me.ComboBox2.Enabled = True
    Me.ComboBox2.BackColor = &HC0FFFF
    Me.ComboBox6.Enabled = True
    Me.ComboBox6.BackColor = &HC0FFFF
  Else
    Me.ComboBox2.Enabled = False
    Me.ComboBox2.Value = ""
    Me.ComboBox2.BackColor = &HE0E0E0
    Me.ComboBox6.Enabled = False
    Me.ComboBox6.Value = ""
    Me.ComboBox6.BackColor = &HE0E0E0
    
    Me.CheckBox3.Value = False
    Me.CheckBox4.Value = False
  End If
  Application.ScreenUpdating = True
End Sub

Private Sub CheckBox3_Click()
  Application.ScreenUpdating = False

  If CheckBox3 Then
    
    If Me.CheckBox1.Value = False Or Me.CheckBox2.Value = False Then
      MsgBox "Checkbox needs to be checked."
      Me.CheckBox3.Value = False
      Exit Sub
    End If
    
    Me.ComboBox3.Enabled = True
    Me.ComboBox3.BackColor = &HC0FFFF
    Me.ComboBox8.Enabled = True
    Me.ComboBox8.BackColor = &HC0FFFF
  Else
    Me.ComboBox3.Enabled = False
    Me.ComboBox3.Value = ""
    Me.ComboBox3.BackColor = &HE0E0E0
    Me.ComboBox8.Enabled = False
    Me.ComboBox8.Value = ""
    Me.ComboBox8.BackColor = &HE0E0E0
    
    Me.CheckBox4.Value = False
  End If
  Application.ScreenUpdating = True
End Sub

Private Sub CheckBox4_Click()
  Application.ScreenUpdating = False
  If CheckBox4 Then
  
    If Me.CheckBox1.Value = False Or Me.CheckBox2.Value = False Or Me.CheckBox3.Value = False Then
      MsgBox "Checkbox needs to be checked."
      Me.CheckBox4.Value = False
      Exit Sub
    End If

    Me.ComboBox4.Enabled = True
    Me.ComboBox4.BackColor = &HC0FFFF
    Me.ComboBox7.Enabled = True
    Me.ComboBox7.BackColor = &HC0FFFF
  Else
    Me.ComboBox4.Enabled = False
    Me.ComboBox4.Value = ""
    Me.ComboBox4.BackColor = &HE0E0E0
    Me.ComboBox7.Enabled = False
    Me.ComboBox7.Value = ""
    Me.ComboBox7.BackColor = &HE0E0E0
  End If
  Application.ScreenUpdating = True
End Sub

Also try the following simplified version:

Private Sub CheckBox1_Click()
  Call UpCombo(1, CheckBox1, ComboBox1, ComboBox5)
End Sub

Private Sub CheckBox2_Click()
  If CheckBox2 And Me.CheckBox1 = False Then
    MsgBox "Checkbox needs to be checked."
    CheckBox2 = False
  Else
    Call UpCombo(2, CheckBox2, ComboBox2, ComboBox6)
  End If
End Sub

Private Sub CheckBox3_Click()
  If CheckBox3 And (Me.CheckBox1 = False Or Me.CheckBox2 = False) Then
    MsgBox "Checkbox needs to be checked."
    Me.CheckBox3 = False
  Else
    Call UpCombo(3, CheckBox3, ComboBox3, ComboBox8)
  End If
End Sub

Private Sub CheckBox4_Click()
  If CheckBox4 And (Me.CheckBox1 = False Or Me.CheckBox2 = False Or Me.CheckBox3 = False) Then
    MsgBox "Checkbox needs to be checked."
    Me.CheckBox4 = False
  Else
    Call UpCombo(4, CheckBox4, ComboBox4, ComboBox7)
  End If
End Sub

Sub UpCombo(s As Long, chBox As MSForms.CheckBox, cmbA As MSForms.ComboBox, cmbB As MSForms.ComboBox)
  cmbA.Enabled = chBox
  cmbB.Enabled = chBox
  cmbA.BackColor = &HC0FFFF
  cmbB.BackColor = &HC0FFFF
  If chBox = False Then
    cmbA.Value = ""
    cmbA.BackColor = &HE0E0E0
    cmbB.Value = ""
    cmbB.BackColor = &HE0E0E0

    If s < 2 Then Me.CheckBox2.Value = False
    If s < 3 Then Me.CheckBox3.Value = False
    If s < 4 Then Me.CheckBox4.Value = False
  End If
End Sub

 

[cydmm]

Wonderful solutions Dante, they both work like a charme. Thank you so much. ?
?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.