Need help trying to hide rows based on a checkbox in excel

[denniskillz]

Hi,

I have written some code in order to hide rows based on a checkbox. Initially I wrote the code to hide rows based on a value in a cell and it worked fine, however when i moved to a checkbox it did not work. I have pasted part of the code below.

Private Sub Worksheet_change(ByVal Target As Range)
ActiveSheet.Activate

If Not Application.Intersect(Range("A18"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "TRUE":  Rows("39:47").EntireRow.Hidden = False
                   ' Rows("39:47").EntireRow.Hidden = True
    End Select
  End If

If Not Application.Intersect(Range("A19"), Range(Target.Address)) Is Nothing Then
   Select Case Target.Value
   Case Is = "TRUE":   Rows("49:57").EntireRow.Hidden = False
                   ' Rows("59:67").EntireRow.Hidden = True
    
   End Select
  End If

Also the hide and unhide of rows never worked properly can someone please help.

[Eastw00d]

Hi,
I think you can have better results when you use instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cheers
Erwin

[denniskillz]

Hi,
I think you can have better results when you use instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cheers
Erwin

Thanks so much I will try what you suggest and see how it works out.

[Fluff13]

Hi & welcome to the board.
Rather than checkboxes, which don't trigger a change event, why not just put an X in the cell instead?

[denniskillz]

I tried using X as the trigger in a cell based on the checkbox =TRUE/FALSE and got mixed results. I think I need to work on a better VBA code.

[Eastw00d]

Maybe this is what you are looking for:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Range("A18"), Range(Target.Address)) Is Nothing Then
    Select Case LCase(Target.Value)
    Case Is = "x"
        Rows("39:47").EntireRow.Hidden = False
     Case Is <> "x"
        Rows("39:47").EntireRow.Hidden = True
    End Select
  End If
End Sub

Cheers
Erwin