data netry cell order? (2002 – XP)

Topic

New Reply

Is there a way to control the order of cells visited when the tab key is pressed? I have a worksheet that is protected and the users can only access certain cells. I would like to control the natural order in which they access those cells. Currently, the TAB key moves across the worksheet from left to right and I would like tospecify a particular order of cells.

Thanks

Reply | Quote

Replies

You can use the Worksheet_SelectionChange event routine to do this if the number of cells involved is not hugh. Unfortunately, I can not find any way to find out what cell was previously active, only which cell is being selected. So, the code has to look at what cell is being selected and decide from that what cell should be selected. The code below will move the selection from cell A1 to D1 to A2 to D2 to A1, if the sheet is not protected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Target.Address
        Case "$B$1"
            ActiveSheet.Range("D1").Select
        Case "$E$1"
            ActiveSheet.Range("A2").Select
        Case "$B$2"
            ActiveSheet.Range("D2").Select
        Case Else
            ActiveSheet.Range("A1").Select
    End Select
    Application.EnableEvents = True
End Sub

Another note. This code will drive a user crazy if they are trying to select a cell with the mouse.

Reply | Quote