Disable / automatic enable checkbox based on drop down reaponse

[Adrienne80]

I’m pretty new to VBA, and I’ve tried several different codes to make my sheet work, but no luck. This is a quoting spreadsheet- in column V17 there are 4 options in a drop down- Prosurance, Assurance, Care, MI Only. Then I have 3 check boxes for additional options- cb1, cb2, cb3.
With the Prosurance option, all of the checkboxes should be automatically checked because they’re included. With Care, only cb1 should be enabled, the others disabled. MI only should have all Cb’s disabled, and assurance will have them all enabled. How can I code these to enable/disable/automatically select? I’m using the checkboxes in forms. Thanks in advance!

 

[DanteAmor]

Hi and welcome to MrExcel!

Are the controls on the sheet or in a userform?
If they are on the sheet, I recommend that you use ActiveX controls.
You tell me.

 

[Adrienne80]

They are on the sheet.

 

[DanteAmor]

They are on the sheet.

Are they ActiveX?

 

[Adrienne80]

No I haven’t done them with ActiveX, I will try that. Will that need the code or can I set it in the controls?

 

[DanteAmor]

drop down

That is a Drop-down Lists created with a Data Validation List?

Ok, If the above is correct, then let's work with that and the useform checkboxes.

Put the following code in the sheet events:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "V17" Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Select Case UCase(Target.Value)
      Case UCase("Prosurance")
        ActiveSheet.DrawingObjects("CheckBox1").Value = True
        ActiveSheet.DrawingObjects("CheckBox2").Value = True
        ActiveSheet.DrawingObjects("CheckBox3").Value = True
      Case UCase("Care")
        ActiveSheet.DrawingObjects("CheckBox1").Value = True
        ActiveSheet.DrawingObjects("CheckBox2").Value = False
        ActiveSheet.DrawingObjects("CheckBox3").Value = False
      Case UCase("MI Only")
        ActiveSheet.DrawingObjects("CheckBox1").Value = False
        ActiveSheet.DrawingObjects("CheckBox2").Value = False
        ActiveSheet.DrawingObjects("CheckBox3").Value = False
      Case UCase("Assurance")
        ActiveSheet.DrawingObjects("CheckBox1").Value = True
        ActiveSheet.DrawingObjects("CheckBox2").Value = True
        ActiveSheet.DrawingObjects("CheckBox3").Value = True
    End Select
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Adrienne80]

Yes, the drop down was created with the data validation list. Thanks so much, I’ll work on this in the morning and let you know how it works!

 

[Adrienne80]

That is a Drop-down Lists created with a Data Validation List?

Ok, If the above is correct, then let's work with that and the useform checkboxes.

Put the following code in the sheet events:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "V17" Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Select Case UCase(Target.Value)
      Case UCase("Prosurance")
        ActiveSheet.DrawingObjects("CheckBox1").Value = True
        ActiveSheet.DrawingObjects("CheckBox2").Value = True
        ActiveSheet.DrawingObjects("CheckBox3").Value = True
      Case UCase("Care")
        ActiveSheet.DrawingObjects("CheckBox1").Value = True
        ActiveSheet.DrawingObjects("CheckBox2").Value = False
        ActiveSheet.DrawingObjects("CheckBox3").Value = False
      Case UCase("MI Only")
        ActiveSheet.DrawingObjects("CheckBox1").Value = False
        ActiveSheet.DrawingObjects("CheckBox2").Value = False
        ActiveSheet.DrawingObjects("CheckBox3").Value = False
      Case UCase("Assurance")
        ActiveSheet.DrawingObjects("CheckBox1").Value = True
        ActiveSheet.DrawingObjects("CheckBox2").Value = True
        ActiveSheet.DrawingObjects("CheckBox3").Value = True
    End Select
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

I'm back to working on this project - and this code DOES work, however, it only works for CheckBox 1,2, and 3 (there are 3 columns that the checkboxes are in). How do I make this code work for all checkboxes in the 3 different columns? Thank you