Hide Checkbox based on Cell Value

[bakunawa]

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 selected the Checkbox and Viewed it's code and got this:

Which makes me think that I did use the correct Checkbox name (CheckBox59), but still getting an error. Can anyone enlighten me and help me out? Thanks!

 

[Siddharth Rout]

I thought that maybe I have my checkbox name wrong, so I selected the Checkbox and Viewed it's code and got this:

Looks like it is an ActiveX control and not a Form control

Instead of

ActiveSheet.Shapes("CheckBox59").Visible= False

use

CheckBox59.Visible= False

 

[bakunawa]

Looks like it is an ActiveX control and not a Form control

Instead of

ActiveSheet.Shapes("CheckBox59").Visible= False

use

CheckBox59.Visible= False

Thanks for the quick answer! Yes, im using Form Control checkboxes. I tried what you said above and did this:

but whenever I change L29's value, I get this error message:

 

[Siddharth Rout]

Are you sure it is a form control? If it is, then

ActiveSheet.Shapes("CheckBox59").Visible = False

should work unless the name is is different? Something like

ActiveSheet.Shapes("Check Box 59").Visible = False

You can check the name in the Name Box as shown below

 

[bakunawa]

Are you sure it is a form control? If it is, then

ActiveSheet.Shapes("CheckBox59").Visible = False

should work unless the name is is different? Something like

ActiveSheet.Shapes("Check Box 59").Visible = False

You can check the name in the Name Box as shown below

View attachment 56270

Renaming them did the trick! Thank you so much!

 

[bakunawa]

Are you sure it is a form control? If it is, then

ActiveSheet.Shapes("CheckBox59").Visible = False

should work unless the name is is different? Something like

ActiveSheet.Shapes("Check Box 59").Visible = False

You can check the name in the Name Box as shown below

View attachment 56270

Follow up question!

I need to hide/unhide a different set of checkboxes based on a different (K26) cell Value. Do i just add the formulas below the End Sub like so?

 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
End Sub


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

 

[Siddharth Rout]

Follow up question!

I need to hide/unhide a different set of checkboxes based on a different (K26) cell Value. Do i just add the formulas below the End Sub like so?

 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
End Sub


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

No no. You can use the same proc.

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

 

[excellearner111]

Follow up question to this - I am using the code above, but in my case the cell "k29" (the one being checked for a value of "yes") is a formula that is determined to output "yes" or "no" based on checkboxes that are selected on another sheet. The issue is that when I make the inputs on the other sheet, and the value of my cell "k29" on the other sheet changes to yes, the checkbox does not automatically hide. It looks like it only works if I re-paste the formula so that the change happens while the sheet with the checkbox that is to be hidden is active.

Is there a way to make it so that the checkbox will hide or unhide even if the reference cell is based on changes that are made to another sheet?

 

[excellearner111]

Sorry, I am a very beginner. It looks like the checkbox updates when any change is made on the worksheet. I guess this is because we are under the Worksheet Change event. How can I rewrite the code such that it automatically updates every time I go back to the worksheet from the other worksheet where the changes are made?