CODEWITHSUNDEEP
excelvbaif-statementcheckbox
Wurschti
Wurschti

Reputation: 33

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

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

Upvotes: 0

Views: 3825

Answers (2)

Wurschti
Wurschti

Reputation: 33

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.

Upvotes: 0

kuv
kuv

Reputation: 36

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

Upvotes: 2

Related Questions