Disable checkbox in VBA

[ElvisSteel]

I have a sheet with a number of checkboxes, and I need to conditionally enable/disable some of them.
For example, if the user checks Box1, then the question for Box2 becomes relevant and Box2 becomes available. If Box1 is unchecked, Box2 needs to be unchecked and disabled.
Is this possible and if so, can you please advise how.
Thanks

Steve

 

[dave3009]

Hi Steve

There is an event for handling just this

try:

Private Sub CheckBox1_Change()
Select Case CheckBox1.Value
    Case True: CheckBox2.Enabled = True
    Case False: CheckBox2.Enabled = False
End Select
End Sub

As an example.

 

[Georgiboy]

To uncheck the box also just add...

Private Sub CheckBox1_Change()
Select Case CheckBox1.Value
    Case True: CheckBox2.Enabled = True
    Case False: CheckBox2.Enabled = False: CheckBox2.Value = False
End Select
End Sub

 

[ElvisSteel]

Thank you both for the prompt response

 

[ElvisSteel]

Sorry I was a little premature with the reply, as I cannot get the code you suggested to work.

I am not using a form, just a checkbox on the worksheet. Is there a CheckBox_Change event, and if so, where do I put the code?

Am I missing something obvious?

 

[Colin Legg]

Depends on the type of checkbox it is. Did you add it from the controls toolbox or from the forms toolbar?

 

[ElvisSteel]

I added it from: Developer, Insert, Form Controls

 

[Colin Legg]

Forms controls don't have event handlers, so you will have to do the enabling/disabling from the procedure you assign to it.

 

[ElvisSteel]

OK thanks, but can I still enable/disable a checkbox from within the procedure?
I have assigned a macro (CheckBox1_Click)to the checkbox, and linked it to a cell ("Box1Value")

The following code gives "Run time error 424, Object required"...


Sub CheckBox1_Click()
Select Case Range("Box1Value").Value
Case True: CheckBox2.Enabled = True
Case False: CheckBox2.Enabled = False: CheckBox2.Value = False
End Select
End Sub

 

[RoryA]

Try:

Sub CheckBox1_Click()
Activesheet.Checkboxes("CheckBox2").Enabled = Range("Box1Value").Value
End Sub

<!-- / message -->