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

 

[Colin Legg]

It would be nice to have a generic routine which you can apply to all of the checkboxes (except for the last checkbox which has no dependants), otherwise you would have to write one routine per checkbox. Assuming you have them laid out on the worksheet in the order they were added, you could use something like this:

Sub CheckboxManager()
    Dim i As Long
    Dim lngItem As Long
    Dim blnTicked As Boolean
    
    With Sheet1.CheckBoxes
        
        'which checkbox was changed?
        lngItem = .Item(Application.Caller).Index
        
        'is it ticked?
        blnTicked = (.Item(lngItem).Value = 1)
        
        'if it was ticked then
        'we want to enable the following checkbox
        If blnTicked Then
            .Item(lngItem + 1).Enabled = True
            
        'if it was unticked then
        'we want to disable all the subsequent checkboxes and untick them
        Else
            For i = (lngItem + 1) To .Count
                With .Item(i)
                    .Value = False
                    .Enabled = False
                End With
            Next i
        End If
    End With
    
End Sub

 

[RoryA]

If they were arranged in a regular pattern, you could also do something using the TopLeftCell, which might be safer than the Index.

 

[Colin Legg]

Yeah, index relies on their position order being the same as their creation order.

I was looking at the possible checkbox values and it looks like there are three: Unchecked, (-4146) Checked (1) and Mixed (2). Do you know if there is a built-in class with these enumerated constants available?

 

[ElvisSteel]

I'd love to one day be able to contribute to this discussion, but for now thank you both very much!