2

I have a multiple selection, Option buttons, that change the value of cell D7 from 1 to 5, depending on choice. I want to unhide rows 16 to 26 if value is 1 and hide them if it's different, and so on for every other value.

But I can't even get this to work at all, and I'm not sure what I'm doing wrong.

Update: If I change the cell value, nothing happens, but if I delete all contents and add a value it gives: "Argument not optional", and it highlights this part of the code for me:

Private Sub Worksheet_Change(ByVal Target as Excel.Range)

Thank you

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If IsNumeric(Target) And Target.Address = "$D$7" Then Select Case Target.Value Case 0 To 90: Cell_Hider End Select End If End Sub Sub Cell_Hider(ByVal Target As Range) If Range("$D$7").Value = "1" Then Rows("16:26").EntireRow.Hidden = False Else Rows("16:26").EntireRow.Hidden = True End If End Sub
Improve this question
4
  • "It's not working" is not helpful please edit and improve your question. Commented Jun 2, 2021 at 8:50
  • I dont understand. Nothing happens when I change values, therefor it's not working.. I'm not sure how else I was supposed to phrase it.. Commented Jun 2, 2021 at 8:54
  • 1
    Well telling that it doesn't throw any errors is already more than not telling. That means your Worksheet_Change never excecuted as it should immediately throw an error. Make sure the event code is placed in the correct worksheet (the one you change a value at). Also make sure you have events enabled Application.EnableEvents must not be false. Commented Jun 2, 2021 at 8:57
  • ah, I understand, let me try that and edit the post with what I get. thank you Commented Jun 2, 2021 at 9:07

2 Answers 2

Reset to default
1
  1. Your procedure Cell_Hider needs an argument but your code calls it without argument Case 0 To 90: Cell_Hider
  2. You call Cell_Hider if the value is between 0 and 90 then that procedure needs the value to be 1 to show the rows and 0 or 2 to 90 will hide them. If you put 100 in that cell nothing happens at all. Sounds not like what you expect to me.
  3. "1" is text not a number!

Something like the following would work:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) If IsNumeric(Target) And Target.Address = "$D$7" Then Select Case Target.Value Case 0 To 90: Cell_Hider Target End Select End If End Sub Sub Cell_Hider(ByVal Target As Range) If Target.Value = 1 Then Target.Parent.Rows("16:26").EntireRow.Hidden = False Else Target.Parent.Rows("16:26").EntireRow.Hidden = True End If End Sub

Even though it doesn't look logic to me and I'm not sure what you are exactly trying to achieve.

Note that you can shorten it to

Sub Cell_Hider(ByVal Target As Range) Target.Parent.Rows("16:26").EntireRow.Hidden = Not Target.Value = 1 End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, that's what I needed.
1

Hide/Unhide Rows on Cell Change

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sCell As Range: Set sCell = Me.Range("D7") If Intersect(sCell, Target) Is Nothing Then Exit Sub If IsNumeric(sCell.Value) Then HideRows sCell End If End Sub Sub HideRows(ByVal SourceCell As Range) If SourceCell.Value = 1 Then SourceCell.Worksheet.Rows("16:26").Hidden = False Else SourceCell.Worksheet.Rows("16:26").Hidden = True End If End Sub
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.