0

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
Wurschti
  • 23
  • 2
  • 9
  • 2
    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?](https://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20)) – Pᴇʜ Mar 17 '20 at 10:39
  • Also note that a checked checkbox returns `1` as value but a non-checked returns `-4146` and not `0` as you assume. – Pᴇʜ Mar 17 '20 at 10:44
  • 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 Mar 17 '20 at 10:53
  • 2
    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ᴇʜ Mar 17 '20 at 10:59
  • 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 Mar 17 '20 at 11:13
  • Avoiding duplicate names means avoiding issues. Try to use unique names. If your box's name is `Team_Availability` then naming your procedure `Team_Availability_Click` is ok (that is unique). • I have no idea what you could do wrong. But I can say as I described it, is the correct way to do it, and I tested it and it worked. `Dim cb As Shape` is correct too. – Pᴇʜ Mar 17 '20 at 11:19

2 Answers2

2

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
kuv
  • 36
  • 3
  • Note, missing `End Select`, Also a lot of variables defined that are never used. – Pᴇʜ Mar 17 '20 at 11:23
  • @Pᴇʜ thanks a lot for your patience, I cannot make it run and do really not know what is going wrong. @ kuv I get a compiling error, also using End Select. Also I do not understand what all the variables do. I guess they'll use a lot of memory, especially if I created a lot of similar macros within that sheet and workbook. – Wurschti Mar 17 '20 at 11:34
  • @Wurschti You need to get rid of the `do nothing` this is no valid code and should be a comment instead. If that doesn't fix it please tell which line you get the error. – Pᴇʜ Mar 18 '20 at 12:19
0

I found the error together with a friend. In the top left corner I did not assign the specific name to the Control CheckBox. I had just set the macro/sub name and the description. Now it runs.

Wurschti
  • 23
  • 2
  • 9