How to ask for Checkbox (True/False) in Macro using If-function

Question

How can I hide/show columns and rows in another sheet ("Project Plan") within the same workbook using a checkbox? If the checkbox is checked, they should not be hidden. If the checkbox is not checked, they should be hidden. The checkboxes are in an own sheet ("Guidelines"). I tried the following but get the error "Run time error '424': Object required'"

Sub Team_Availability_Click()

Dim rng As Range

Set rng = ThisWorkbook.Sheets("Project Plan").Rows("5:8")

   If Team_Availability.Value = False Then
    rng.Hidden = True

   ElseIf Team_Availability.Value = True Then
    rng.Hidden = False    

End If
End Sub

Alternatively, I tried out this way, found in a similar question using some kind of object:

Checking if a worksheet-based checkbox is checked

Sub Team_Availability_Click()

Dim rng As Range
Set rng = ThisWorkbook.Sheets("Project Plan").Rows("5:8")

 If ThisWorkbook.Worksheets("Guidelines").Shapes("Team_Availability").OLEFormat.Object.Value = 0 Then
    rng.Hidden = True

 ElseIf ThisWorkbook.Worksheets("Guidelines").OLEFormat.Object.Value = 1 Then
    rng.Hidden = False

End If
End Sub

Here I get the error

The Item with the specified name wasn't found.

I did not introduce the dim/set I guess. Now, this is the newest version:

Now I get the error in in line Set cb = ActiveSheet... saying

The item with the specified name wasn't found.

Sub Team_Availability_Click()

Dim cb As Shape
Dim rng As Range

Set cb = ThisWorkbook.Sheets("Guidelines").Shapes("Team_Availability")
Set rng = ThisWorkbook.Sheets("Project Plan").Rows("5:8")

 If ThisWorkbook.Sheets("Guidelines").Shapes("Team_Availability").OLEFormat.Object.Value = -4146 Then
    rng.Hidden = True

 ElseIf ThisWorkbook.Sheets("Guidelines").Shapes("Team_Availability").OLEFormat.Object.Value = 1 Then
    rng.Hidden = False

 End If

End Sub

That error means a shape named Team_Availability does not exist in ActiveSheet. Avoid using ActiveSheet instead access your sheet by name ThisWorkbook.Worksheets("SheetName") which is much more reliable. Then check your shape name if it really is exactly Team_Availability and doesn't have typos. Also make sure it is a FormControl and not an ActiveX Control (see What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?) — Pᴇʜ
Also note that a checked checkbox returns 1 as value but a non-checked returns -4146 and not 0 as you assume. — Pᴇʜ
Thanks for your help! I am using FormControl, just double-checked it. Also, I changed the ActiveSheet for the Sheet's name. I've put the name/textbox description to "Team_Availability". The Sub is "Team_Availability_Click()". Still I get the same error. — Wurschti
You must be doing something wrong or oversee something. If I open up a new workbook nama a worksheet Guidelines add a Form Control CheckBox name it Team_Availability and run Set cb = ThisWorkbook.Sheets("Guidelines").Shapes("Team_Availability") this works with no error. You must be doing something wrong in that process. The code looks good. Check it out on a new workbook. The issue must be somewhere else or a typo (or additional space) somwhere. — Pᴇʜ
I tried it. Still the same result. I introduced it as > Dim cb As Shape as shown above. Is that right? I copy/pasted the names to make sure there are no typos. Does it make a difference if I call it: > Sub Team_Availability_Click() > Sub Team_Availability() — Wurschti

kuv kuv · Accepted Answer · 2020-03-17T11:22:24

I've looked at your code and didn't really work when I tried it. This code worked for the task you describes hope it helps.

Sub CheckBoxHIDE()
    Dim ws As Worksheet
    Dim chk As CheckBox

    Set ws = ActiveSheet
    Set chk = ws.CheckBoxes(Application.Caller)

    Select Case chk.Value
       Case 1   'box is checked
          Columns("D").Hidden = True

       Case Else   'box is not checked
          'do nothing 

    End Select
End Sub

How to ask for Checkbox (True/False) in Macro using If-function

Checking if a worksheet-based checkbox is checked

2 Answers