I've set up a worksheet that will allow the user to select preset options from a data validation list in a cell (B23). The user's selection off this one cell will then trigger certain rows to be unhidden, guiding the user to input data into only the appropriate rows. The VBA code I am using for unhiding the appropriate rows works fine, EXCEPT for the fact that whenever a user enters data into one of the unhidden rows, all the rows will hide. The user then needs to reselect their option from the initial data validation list (cell B23) to unhide the appropriate rows again. The value they enter is entered and saved in the cell, but they need to reselect their initial choice between each data entry step, which is annoying and what I'd like to fix.
I suspect that:
1)the VBA code I hobbled together doesn't account for the fact that users will be entering data into rows that are hidden / will be triggered to unhide, and/or
2) the crux of the problem is refreshing the cell with the data validation list (B23) that triggers which rows to be unhidden. I unfortunately have no idea how to do this.
Any help would be very much appreciated!!
My VBA code to hide rows is below.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim allRows As Range
Set allRows = Rows("27:64")
allRows.Hidden = True
If Not Intersect(Target, Range("B23")) Is Nothing Then
If Target.Value = "A1" Then
Rows("27:31").Hidden = False
ElseIf (Target.Value = "A10-A-S" Or Target.Value = "A10-A-P" Or Target.Value = "A10-A-T") Then
Rows("32:36").Hidden = False
ElseIf (Target.Value = "A10-B-S" Or Target.Value = "A10-B-P" Or Target.Value = "A10-B-T") Then
Rows("37:44").Hidden = False
ElseIf (Target.Value = "E19S" Or Target.Value = "E19P" Or Target.Value = "E19T") Then
Rows("46:54").Hidden = False
ElseIf (Target.Value = "E20S" Or Target.Value = "E20P" Or Target.Value = "E20T") Then
Rows("56:64").Hidden = Fal