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:
But, I'd do it like this: