whothemannow
New Member
- Joined
- Feb 23, 2021
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
- MacOS
Hi everyone,
I've tried a number of solutions online and I can't seem to find one that actually works for me. I am trying to combine two WorksheetChange events on the same sheet in VBA. I am a newcomer to VBA, so I might be missing something, or just a more efficient way that I could be doing this.
Essentially, I want to have 2 events which largely are the same concept, depending on the input of a certain cell, this hides or shows different rows.
Here, I want to show Row 17 when the value in C10 is Yes, 11-16 when it is No, and nothing when it is Select One.
I want to combine this with:
Here, I want Row 22 to be shown when C19 is Yes, 20-21 when it is No, and nothing when it is Select One.
Thanks in advance!
I've tried a number of solutions online and I can't seem to find one that actually works for me. I am trying to combine two WorksheetChange events on the same sheet in VBA. I am a newcomer to VBA, so I might be missing something, or just a more efficient way that I could be doing this.
Essentially, I want to have 2 events which largely are the same concept, depending on the input of a certain cell, this hides or shows different rows.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C10")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
ElseIf Range("C10").Value = "Select One" Then
Rows("11:17").EntireRow.Hidden = True
ElseIf Range("C8").Value = "Yes" Then
Rows("11:16").EntireRow.Hidden = False
Rows("17").EntireRow.Hidden = True
ElseIf Range("C8").Value = "No" Then
Rows("11:16").EntireRow.Hidden = True
Rows("17").EntireRow.Hidden = False
End If
End Sub
Here, I want to show Row 17 when the value in C10 is Yes, 11-16 when it is No, and nothing when it is Select One.
I want to combine this with:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C19")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
ElseIf Range("C19").Value = "Select One" Then
Rows("20:22").EntireRow.Hidden = True
ElseIf Range("C19").Value = "Yes" Then
Rows("21").EntireRow.Hidden = False
Rows("22").EntireRow.Hidden = True
ElseIf Range("C19").Value = "No" Then
Rows("20:21").EntireRow.Hidden = True
Rows("22").EntireRow.Hidden = False
End If
End Sub
Thanks in advance!