Excel – Show or hide rows in an excel sheet subject to the value of a particular cell (excel 2007 & Windows 7)

microsoft-excel-2007vba

I have made a spreadsheet to specify decoration selections from a range of standard options.

There are five separate ranges so this is the first selection then within each range there are additional selections.

So at the start of my sheet I have used a ‘frame’ and five ‘radio buttons’ and I have linked this selection to cell P1.

So once this selection is made then I want to hide various rows and only show the selections relevant to that first selection (rows 10-19 relevant to range 1, rows 20-29 relevant to range 2 etc.).

Below is my ‘code’ but it does nothing when I change radio button selections (I can see the value in cell P1 changing as expected).

Sub HideShow()
'
' HideShow Macro
' Show only the selection pane assocated to the Design line selection


If Range("P1").Value = 1 Then  ‘Decoration Range A
        Rows("10:19").Select
        Selection.EntireRow.Hidden = False
        Rows("20:59").Select
        Selection.EntireRow.Hidden = True

ElseIf Range("P1").Value = 2 Then  'Decoration Range B
        Rows("10:19").Select
        Selection.EntireRow.Hidden = True
        Rows("20:29").Select
        Selection.EntireRow.Hidden = False
        Rows("30:59").Select
        Selection.EntireRow.Hidden = False

ElseIf Range("P1").Value = 3 Then  'Decoration Range C
        Rows("10:29").Select
        Selection.EntireRow.Hidden = True
        Rows("30:39").Select
        Selection.EntireRow.Hidden = False
        Rows("40:59").Select
        Selection.EntireRow.Hidden = False

ElseIf Range("P1").Value = 4 Then  'Decoration Range D
        Rows("10:39").Select
        Selection.EntireRow.Hidden = True
        Rows("40:49").Select
        Selection.EntireRow.Hidden = False
        Rows("50:59").Select
        Selection.EntireRow.Hidden = False

ElseIf Range("P1").Value = 5 Then  'Decoration Range E
        Rows("10:49").Select
        Selection.EntireRow.Hidden = True
        Rows("50:59").Select
        Selection.EntireRow.Hidden = False
End If


End Sub

I know I probably don't really need the final selection (if it is not A, B, C or D then it can only be E) but I just put it in for neatness.

I also tried with the cell value as text i.e. ElseIf Range("P1").Value = "5" Then... but no joy there also.

Any guidance appreciated.

Best Answer

If you want your code to run on a radio button selection, you need to either call the macro with the radio button or you need to have the macro be private sub worksheet_change() to run on worksheet changes.

The code itself runs fine. There are better ways to do it, but there's nothing wrong with your code.

Put this in the VBA for the sheet you're working with:

Private Sub worksheet_change(ByVal Target As Range)

'
' HideShow Macro
' Show only the selection pane assocated to the Design line selection

If Not Intersect(Target, Cells(1, 16)) Is Nothing Then

    If Range("P1").Value = 1 Then
        'Decoration Range A
        Rows("10:19").Select
        Selection.EntireRow.Hidden = False
        Rows("20:59").Select
        Selection.EntireRow.Hidden = True

    ElseIf Range("P1").Value = 2 Then  'Decoration Range B
        Rows("10:19").Select
        Selection.EntireRow.Hidden = True
        Rows("20:29").Select
        Selection.EntireRow.Hidden = False
        Rows("30:59").Select
        Selection.EntireRow.Hidden = False

    ElseIf Range("P1").Value = 3 Then  'Decoration Range C
        Rows("10:29").Select
        Selection.EntireRow.Hidden = True
        Rows("30:39").Select
        Selection.EntireRow.Hidden = False
        Rows("40:59").Select
        Selection.EntireRow.Hidden = False

    ElseIf Range("P1").Value = 4 Then  'Decoration Range D
        Rows("10:39").Select
        Selection.EntireRow.Hidden = True
        Rows("40:49").Select
        Selection.EntireRow.Hidden = False
        Rows("50:59").Select
        Selection.EntireRow.Hidden = False

    ElseIf Range("P1").Value = 5 Then  'Decoration Range E
        Rows("10:49").Select
        Selection.EntireRow.Hidden = True
        Rows("50:59").Select
        Selection.EntireRow.Hidden = False
    End If

End If

End Sub

But, I'd do it like this:

Private Sub worksheet_change(ByVal Target As Range)

'
' HideShow Macro
' Show only the selection pane assocated to the Design line selection

If Not Intersect(Target, Range("p1")) Is Nothing Then
    On Error Resume Next
    ActiveSheet.Cells.EntireRow.Hidden = False

    If Range("P1") = 1 Then 'Decoration Range A
        Rows("20:59").Hidden = True

    ElseIf Range("P1") = 2 Then  'Decoration Range B
        Rows("10:19").Hidden = True

    ElseIf Range("P1") = 3 Then  'Decoration Range C
        Rows("10:29").Hidden = True

    ElseIf Range("P1") = 4 Then  'Decoration Range D
        Rows("10:39").Hidden = True

    ElseIf Range("P1") = 5 Then  'Decoration Range E
        Rows("10:49").Hidden = True

    End If

End If

End Sub
Related Question