Hide/Unhide rows based on 2 checkboxes

[thekamel]

Hello,

I am trying to hide/unhide certain rows based on checkbox. I am using Form Control checkboxes. I am using the following VBA code for single checkboxes:

Sub CheckBox17_Click()
If ThisWorkbook.Sheets(1).CheckBoxes("Check Box 17").Value = 1 Then
ThisWorkbook.Sheets(1).Rows("34:40").Hidden = False
Else
ThisWorkbook.Sheets(1).Rows("34:40").Hidden = True
End If
End Sub

But now I need to do Rows 2-5 and 10-15 off the same checkbox. How can I combine the row to hide/unhide what I need?

 

[rollis13]

Since your macro is called CheckBox17_Click I presume that you are using a UserForm Checkbox so this is how your macro in the UserForm module should be, elsewise note the use of Union and adapt it to your case:

Option Explicit
Private Sub CheckBox17_Click()
    Dim rng As Range
    With ThisWorkbook.Sheets(1)
        Set rng = Union(.Rows("34:40"), .Rows("2:5"), .Rows("10:15"))
        If UserForm1.CheckBox17.Value = True Then
            rng.EntireRow.Hidden = False
        Else
            rng.EntireRow.Hidden = True
        End If
    End With
End Sub

 

[thekamel]

Since your macro is called CheckBox17_Click I presume that you are using a UserForm Checkbox so this is how your macro in the UserForm module should be, elsewise note the use of Union and adapt it to your case:

Option Explicit
Private Sub CheckBox17_Click()
    Dim rng As Range
    With ThisWorkbook.Sheets(1)
        Set rng = Union(.Rows("34:40"), .Rows("2:5"), .Rows("10:15"))
        If UserForm1.CheckBox17.Value = True Then
            rng.EntireRow.Hidden = False
        Else
            rng.EntireRow.Hidden = True
        End If
    End With
End Sub

When using the code, I am getting a "Run-time error 424: Object Required". The script I have is

Option Explicit
Private Sub CheckBox10_Click()
Dim rng As Range
With ThisWorkbook.Sheets(1)
Set rng = Union(.Rows("36:41"), .Rows("23:24"))
If UserForm1.CheckBox10.Value = True Then
rng.EntireRow.Hidden = False
Else
rng.EntireRow.Hidden = True
End If
End With
End Sub

I cannot figure out how to get it to function properly.

 

[Fluff]

How about

Sub CheckBox17_Click()
   With ThisWorkbook.Sheets(1)
   Debug.Print .CheckBoxes("Check Box 17").Value
      .Rows("23:24").Hidden = .CheckBoxes("Check Box 17").Value <> 1
      .Rows("36:41").Hidden = .CheckBoxes("Check Box 17").Value <> 1
   End With
End Sub

 

[rollis13]

I cannot figure out how to get it to function properly.

Sorry, neither do I without seeing your file. Your code works for me as long that you have a form named "UserForm1" and a checkbox named "CheckBox10" in it.

 

[Fluff]

@rollis13 maybe you missed this

I am using Form Control checkboxes.

So they are controls on a sheet & not a userform.

 

[rollis13]

Yes Fluff, but as said in post #2 I wasn't so sure. Anyway, even without an userform I couldn't replicate error: "Run-time error 424: Object Required"

 

[Fluff]

You'll get that error if you do not have a userform called userform1 & you are not running Option Explicit

 

[rollis13]

Got it, that's why I always use Option Explicit .