Home Ask Login Register

Developers Planet

Your answer is one click away!

aju February 2016

unhiding rows based on cell value for data entry - refresh error

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        


Wyatt Shipman February 2016

You should check if the target is B23 and if not Exit Sub. Maybe Something like this.

   If Intersect(Target, Range("B23")) Is Nothing Then
      Exit Sub
   End If

Phil.Wheeler February 2016

You're right that as soon as anyone changes anything on the worksheet, the Worksheet_Change event will fire and hide those rows.

A simple option would probably be to wrap that code around a check that the change was triggered by your dropdown:

With Target
    If .Count = 1 Then
        If .Row = 23 And .Column = 2 Then ' Assumes your list result is B23?
           allRows.Hidden = True
           ' do your other checks and updates here...
        End If    
    End If
End With

Post Status

Asked in February 2016
Viewed 3,275 times
Voted 13
Answered 2 times


Leave an answer

Quote of the day: live life