mardi 17 mars 2020

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."




Aucun commentaire:

Enregistrer un commentaire