geting a cell to take focus

[Phil-e]

some how I lost the last thread.
What I have done is to hide all the rows except a few. When the user enters data in a cell (by means of a data validation drop down box or by direct entry), more rows are unhidden (depending on thier answer) to allow more data to be entered. I would like the next cell (where date is to be entered) to take focus when the user hits Enter. I tried to lock all cells except the cells that data is to be entered in and protect the workbook, but when I did the hidden rowes would not unhid when data was entered. here is the code that I am using to hide / unhide thr rows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Application.ShowWindowsInTaskbar = False

Select Case Range("M5").Value
Case "B"
Rows("7:54").EntireRow.Hidden = True
Case "A"
Rows("7:9").EntireRow.Hidden = False
Rows("10:54").EntireRow.Hidden = True
End Select

Select Case Range("G9").Value
Case "NO"
Rows("19:22").EntireRow.Hidden = False
Case "YES"
Rows("10:13").EntireRow.Hidden = False
End Select

Select Case Range("G13").Value
Case "YES"
Rows("19:22").EntireRow.Hidden = False
Case "NO"
Rows("14:17").EntireRow.Hidden = False
End Select

Select Case Range("M17").Value
Case "B"

Case "A"
Rows("18:22").EntireRow.Hidden = False
End Select

Select Case Range("G21").Value
Case "NO"
Rows("26:30").EntireRow.Hidden = False
Case "YES"
Rows("22:25").EntireRow.Hidden = False
End Select

Select Case Range("G21").Value
Case "NO"
Rows("26:30").EntireRow.Hidden = False
End Select

Select Case Range("M25").Value
Case "A"
Rows("26:29").EntireRow.Hidden = False
Case "B"
End Select

Select Case Range("G29").Value
Case "NO"
Rows("30:37").EntireRow.Hidden = False
Case "YES"
Rows("30:45").EntireRow.Hidden = False
End Select

Select Case Range("M37").Value
Case "A"
Rows("46:50").EntireRow.Hidden = False
End Select

Application.ScreenUpdating = True
Application.ShowWindowsInTaskbar = True
End Sub

I ned the cell focus to be B5 to H5 to G9 to G13 etc..

Any help?

 

[acw]

Hi

As part of the case statements, why don't you select the next cell to be completed? Range("B5").select???

Also, this event is triggering every time you change a cell. Why don't you add the line
If Intersect(Target, Range("M5,G9,G13,M17,G21,M25,G29,M37")) Is Nothing Then Exit Sub
as the first line of the sub. That way it will only work when one of the required cells is actioned.


Tony