Hide Checkbox based on cell value

[robertdodson1979]

Hopefully this will be a quick fix question.

I am attempting to hide a dozen checkboxes based on the value of a cell being "1" (results of a true/false expression). This seems like such a simple task, but I am failing anyway. I realize it is probably a syntax issue, but I'm not seeing it, probably because I am pretty green on VBA. I DID search first, but results mostly referred to using another checkbox and such

Private Sub HideCheckboxes(ByVal Target As Range)
    If ActiveSheet.Range("B1").Value = "1" Then
        ActiveSheet.CheckBoxes("CheckBox1").Visible = False
    Else
        ActiveSheet.CheckBoxes("CheckBox1").Visible = True
    End If
End Sub

Thanks in advance for you time!

 

[Momentman]

You haven't said what exactly doesnt work and on what line it fails.

Try without putting the "1" in quotes.

Private Sub HideCheckboxes(ByVal Target As Range)
    If ActiveSheet.Range("B1").Value = 1 Then
        ActiveSheet.CheckBoxes("CheckBox1").Visible = False
    Else
        ActiveSheet.CheckBoxes("CheckBox1").Visible = True
    End If
End Sub

 

[robertdodson1979]

Sorry about that.

It does nothing, even after I exit the VBA window and change "B1" from "0" to "1" and back a couple times. No visible error. Also nothing if I try the "Run" button, just a window that wants me to pick a Macro to run

Also, I did try the "1" without quotes to no avail

 

[Momentman]

I have tried it in two ways

Sub changecheck()
 If ActiveSheet.Range("B1").Value = 1 Then
        ActiveSheet.Shapes("CheckBox1").Visible = False
    Else
        ActiveSheet.Shapes("CheckBox1").Visible = True
    End If
End Sub

or with the worksheet calculate event. Right click the sheet tab and select view code and paste this code in there

Private Sub Worksheet_Calculate()
    If ActiveSheet.Range("B1").Value = 1 Then
        ActiveSheet.Shapes("CheckBox1").Visible = True
    Else
        ActiveSheet.Shapes("CheckBox1").Visible = False
    End If
End Sub

Both codes work

Just ensure your checkbox is named "CheckBox1" and not "Check Box 1"

 

[robertdodson1979]

I'm sorry for delay in responding. Things got a little hectic at work.

I'm not sure what I may have done wrong, but they didn't seem to function for me. (Yes, I verified the checkbox name)

However, thank you for mentioning the use of "Shapes" instead of the "Checkboxes" that I had used.

Using "Shapes" and a couple things that I scraped up from an old project of mine, it seems to be working as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("A1") Then
        If ActiveSheet.Range("A1").Value = 1 Then
            ActiveSheet.Shapes("CheckBox1").Visible = False
        Else
            ActiveSheet.Shapes("CheckBox1").Visible = True
        End If
    End If
End Sub

That works for me when I change the value in "A1"

I then moved my reference value to "K1" and decided that I would like to make the routine run when a value is selected in "ComboBox1"

This did the trick for me:

Private Sub ComboBox1_Change()
    If ActiveSheet.Range("K1").Value = 1 Then
        ActiveSheet.Shapes("CheckBox1").Visible = False
    Else
        ActiveSheet.Shapes("CheckBox1").Visible = True
    End If
End Sub

Thanks for the nudge in the right direction!

 

[Momentman]

You are welcome. Glad to help