CODEWITHSUNDEEP
excelvbacheckbox
Josh
Josh

Reputation: 77

Unhide/Hide rows in excel with a form checkbox

I have a form created in excel which has rows [10:48] hidden and I want to make so that when you click a checkbox rows [10:48] are unhidden. I assigned a macro to the checkbox and using this formula:

Private Sub CheckBox45_Click()

  If CheckBox45 = True Then

  [10:48].EntireRow.Hidden = False

  Else: [10:48].EntireRow.Hidden = True

End If

End Sub

When I click the checkbox nothing happen, but when I unhide the rows and click the checkbox it hides the rows. Which makes me think that only one of the actions is working. Is there a way to fix this?

Thanks in advance for the help.

Don't know if this matters but the form checkbox is in column D row 6

Upvotes: 2

Views: 6556

Answers (2)

urdearboy
urdearboy

Reputation: 14580

Here is another approach.

The statement ws.CheckBoxes("Check Box 30") = 1 will either return TRUE or FALSE which will either hide, or unhide, your target rows. 

Private Sub CheckBox30_Click()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Range("A10:A48").EntireRow.Hidden = ws.CheckBoxes("Check Box 30") = 1

End Sub

Upvotes: 0

ArcherBird
ArcherBird

Reputation: 2134

This assumes you are hiding/unhiding rows on Sheet 1 and the checkbox belongs to sheet 1 of the workbook, then:

Private Sub CheckBox30_Click()  
    If ThisWorkbook.Sheets(1).CheckBoxes("Check Box 30").Value = 1 Then
        ThisWorkbook.Sheets(1).Rows("10:48").Hidden = true 
    Else
        ThisWorkbook.Sheets(1).Rows("10:48").Hidden = false
    End If
End Sub

Upvotes: 1

Related Questions