Hide and uncheck checkbox based on cell value

[povictory]

Hello everyone!

I have a sheet where I have some checkboxes that I want to appear/disappear based on an input in another cell. I found the thread and code below that solved that for me, however I am also wanting the boxes to be unchecked in the case where they disappear. I figured it would be a fairly simple modification to the code below, even for a newbie like me, but anything I've tried seems to break it and I can't figure out a way that works. Any input on what modifications I can make to the code below to automatically uncheck the boxes when K9 does *not* = "Yes" would be greatly appreciated!

Here is the original thread:

Hide Checkbox based on Cell Value

Hi everyone! I'm working on an Excel Sheet where checkboxes need to be hidden based on (L29) Cell Value (Yes/No) I've done some digging and came up with this: However, when I run change the Value in Cell L29, i get this error: I thought that maybe I have my checkbox name wrong, so I...

www.mrexcel.com

Here is the original code that that I would like to modify:

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveSheet.Range("k29").Value = "Yes" Then
        ActiveSheet.Shapes("CheckBox1").Visible = True
        ActiveSheet.Shapes("CheckBox2").Visible = True
    Else
        ActiveSheet.Shapes("CheckBox1").Visible = False
        ActiveSheet.Shapes("CheckBox2").Visible = False
    End If
   
    If ActiveSheet.Range("k26").Value = "Yes" Then
        ActiveSheet.Shapes("CheckBox3").Visible = True
    Else
        ActiveSheet.Shapes("CheckBox3").Visible = False
    End If
End Sub

 

[John_w]

Any input on what modifications I can make to the code below to automatically uncheck the boxes when K9 does *not* = "Yes"

The OP in the linked thread says their checkboxes are Form Control checkboxes, rather than ActiveX Control checkboxes, however you haven't clarified your type. Assuming they're Form Control checkboxes then try this code in the sheet module of the sheet where the checkboxes reside:

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet
        If .Range("K9").Value = "Yes" Then
            .CheckBoxes("Check Box 1").Visible = True
            .CheckBoxes("Check Box 2").Visible = True
        Else
            'Hide and uncheck check boxes
            .CheckBoxes("Check Box 1").Visible = False
            .CheckBoxes("Check Box 1").Value = xlOff
            .CheckBoxes("Check Box 2").Visible = False
            .CheckBoxes("Check Box 2").Value = xlOff
        End If
    End With
End Sub

Note - your description says K9, but your code is looking at K29.

 

[povictory]

The OP in the linked thread says their checkboxes are Form Control checkboxes, rather than ActiveX Control checkboxes, however you haven't clarified your type. Assuming they're Form Control checkboxes then try this code in the sheet module of the sheet where the checkboxes reside:

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet
        If .Range("K9").Value = "Yes" Then
            .CheckBoxes("Check Box 1").Visible = True
            .CheckBoxes("Check Box 2").Visible = True
        Else
            'Hide and uncheck check boxes
            .CheckBoxes("Check Box 1").Visible = False
            .CheckBoxes("Check Box 1").Value = xlOff
            .CheckBoxes("Check Box 2").Visible = False
            .CheckBoxes("Check Box 2").Value = xlOff
        End If
    End With
End Sub

Note - your description says K9, but your code is looking at K29.

You are correct that they are Form Control checkboxes and this works perfectly! Also, apologies as I did miskey K9 instead of K29.

Thank you so much for this solution...it's much appreciated!