Disable checkbox on cell value

[AllanSull]

Is there a way to disable a checkbox if say A1="No"?

 

[Jon von der Heyden]

In your previous code CheckboxAirSus is still an implicit empty variable...

Try;

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ctlCheckBox     As CheckBox
    
    With Me
        Set ctlCheckBox = .CheckBoxes("CheckboxAirSus")
        ctlCheckBox.Enabled = Not .Range("G55").Value = "No"
    End With
End Sub

 

[AllanSull]

Thank you, VBA newbie so learning all the time. I've amended the code to the following so the checkbox visibly looks disabled.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ctlCheckBox     As CheckBox
    With Me
        Set ctlCheckBox = .CheckBoxes("CheckboxAirSus")
        ctlCheckBox.Enabled = Not .Range("G55").Value = "No"
        If Range("G55") = "No" Then
        ctlCheckBox.Value = 2
        Else
        ctlCheckBox.Value = 0
        End If
    End With
End Sub

When I want to use this on further checkboxes I've tried this code but it doesn't work, will it only work on one?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ctlCheckBox     As CheckBox
    With Me
        Set ctlCheckBox = .CheckBoxes("CheckboxAirSus")
        ctlCheckBox.Enabled = Not .Range("G55").Value = "No"
        If Range("G55") = "No" Then
        ctlCheckBox.Value = 2
        Else
        ctlCheckBox.Value = 0
        End If
    End With
    With Me
        Set ctlCheckBox = .CheckBoxes("CheckboxCW100")
        ctlCheckBox.Enabled = Not .Range("G51").Value = "No"
        If Range("G51") = "No" Then
        ctlCheckBox.Value = 2
        Else
        ctlCheckBox.Value = 0
        End If
    End With
End Sub

 

[Jon von der Heyden]

It should work. Do you get an error message?

 

[AllanSull]

Think I see the problem, the "No" in G51 is an If statement that gives "No" if any other checkbox is ticked (only allowed to tick one). But this doesn't get recognised as a worksheet change?

 

[AllanSull]

Discovered the worksheet_calculate which works, however whenever I click the checkbox it won't tick, disspears straight away using the above code?

 

[Jon von der Heyden]

disappears? like becomes invisible?

 

[AllanSull]

As in the state doesn't stay. When I take out the following code it works, is there any way to return the checkbox to 0 without it doing so when the checkbox is clicked too? Seems like a loop.

      If Range("G55") = "No" Then
        ctlCheckBox.Value = 2
        Else
        ctlCheckBox.Value = 0
        End If

 

[Jon von der Heyden]

Oh I think I see what is going on. At the start of your code enter:

Application.EnableEvents = False

And at the end enter:

Application.EnableEvents = True

 

[AllanSull]

This still results in the checkbox unchecking itself when clicked?

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    Dim ctlCheckBox     As CheckBox
    With Me
        Set ctlCheckBox = .CheckBoxes("CheckboxCW100")
        ctlCheckBox.Enabled = Not .Range("G51").Value = "No"
        If Range("G51") = "No" Then
        ctlCheckBox.Value = 2
        Else
        ctlCheckBox.Value = 0
        End If
    End With
Application.EnableEvents = True
End Sub

 

[Jon von der Heyden]

Your code says that if G51 is No then tick the checkbox, otherwise untick it. So when calc kicks-off and G51 is No, is the checkbox ticked? If so then that's exactly what your code tells it to do.

Perhaps you should explain the process in a little more detail because I'm struggling to grasp what you expect from this code.