Controlling the Cursor: Position_Curosr_In_Data

The last few posts covered Worksheet_Change and Worksheet_SelectionChange events.  Both rely on a function called Position_Cursor_In_Data.  Position_Cursor_In_Data‘s Job is to jump over ‘locked’ cells and place the cursor in the next ‘unlocked’ cell.  This prevents the user from inadvertently changing things that won’t get updated, or in other words, wasting their time. 

Since the purpose of the routine is to jump over ‘locked’ cells, the routine first checks to see if the cursor has moved into a ‘locked’ cell.  If the cursor is in an ‘unlocked’ cell there is nothing for the routine to do and so, it ends (exits).

Another excuse for the routine to end is if the user has selected a group of cells.  This may be a prelude to a copy or paste command.  I want to facilitate copy/paste commands so if more than one cell is selected, this routine doesn’t interfere.

There is one last excuse for this routine to end itself and that is if the user used the mouse to navigate to a ‘locked’ cell.  If the user really wants to position the cursor in a single ‘locked’ cell, it may also be for the purpose of copy/paste.  It could also be because the user really doesn’t know what they’re doing.  That’s okay.  Remember that If the user tries to change ‘locked’ cells Worksheet_Change will ‘undo’ their change and restore ’locked’ cell values.  Neat huh?

So the user has pressed a key and ended up in a ‘locked’ cell.  The key to knowing where to jump to is in knowing which key the user pressed.  If they pressed RIGHT, TAB, ENTER, DOWN, or PAGEDOWN the system assumes they want the next ‘unlocked’ cell.  If there is no ‘unlocked’ cell to the right, the routine searches below starting in the left most position and looking right for the next ‘unlocked’ cell.  If the pressed LEFT, SHIFT TAB, UP, or PAGEUP the system assumes they want the previous ‘unlocked’ cell.  In that case, the search moves left, and if need be, up starting in the last cell of the previous line and looking left.

Below is the code for Position_Cursor_In_Data.  It relies on Find_UnLocked_Cell.  That will be the topic of our next post.

Function Position_Cursor_In_Data(Cell As Range, _
                                 Entries As Range, _
                                 KeyPressed As String) As Boolean

'   Description:Call this from Worksheet_SelectionChange to force _
                cursor positions inside the entry area

'   Parameters: Cell       Current cell or range selected by user
'               Entries    Range to restrict the cursor to
'               KeyPressed Last key the user pressed

'   Example:    bResult = Position_Cursor_In_Data( _
                            Target, Range("Data"), KeyPressed)

'   Abstract:   If the cursor is moved to a locked cell via
'               keyboard, move the cursor to the next unlocked cell.

'     Date   Init Modification
'   01/12/06 CWH  Initial Programming

    On Error GoTo ErrHandler            '
    Position_Cursor_In_Data = Success   'Assume the Best

'   If more than 1 cell is selected, don't do anything
    If Cell.Rows.Count > 1 Or Cell.Columns.Count > 1 Then _
        Exit Function
'   If the Cell is unlocked, we're done
    If Cell.Interior.Color <> CellLocked Then Exit Function
   
   'From last key pressed, determine direction to _
    search for an unlocked cell
    Dim sLocateMethod As String
    Select Case KeyPressed
        Case Is = "Up", "PageUp", "Left", "ShiftTab"
            sLocateMethod = "Previous"
        Case Is = "Down", "PageDown", "Right", "Tab", "Return"
            sLocateMethod = "Next"
        Case Else
            Exit Function
    End Select
   
'   End looking for an excuse to leave early
               
    Settings "Save"         'Save current application settings
    Settings "Disable"      'Disable events, screen updates & calc.s
   
    Dim lRow As Long
    Dim lCol As Long
    Dim bfound As Boolean
                       
    Dim lRight As Long      'Last allowable column
    Dim lBottom As Long     'Last allowable row
   
    lRight = Entries.Column + Entries.Columns.Count - 1
    lBottom = Entries.Row + Entries.Rows.Count
                
    If sLocateMethod = "Next" Then
       'Search to the right on same row
        bfound = Find_UnLocked_Cell(Cell.Row, Cell.Row, _
                                    Cell.Column + 1, lRight, 1)
       'Search rows below
        If Not bfound Then _
            bfound = Find_UnLocked_Cell(Cell.Row + 1, lBottom, _
                                        1, lRight, 1)
    End If
   
   'We're here, either because there's nothing below, _
    or we want to check previous
   'Search to the left on same row
    If Not bfound Then
        If Cell.Column > 1 Then _
            bfound = Find_UnLocked_Cell(Cell.Row, Cell.Row, _
                                        Cell.Column - 1, 1, -1)
    End If
    'Search rows above
    If Not bfound Then
        If Cell.Row > 1 Then _
            bfound = Find_UnLocked_Cell(Cell.Row - 1, 1, _
                                        lRight, 1, -1)
    End If
   
   'We're here because we looked previous & found nothing or _
    there's just nothing here
   'Search to the right on same row
    If Not bfound Then _
        bfound = Find_UnLocked_Cell(Cell.Row, Cell.Row, _
                                    Cell.Column + 1, lRight, 1)
   'Search rows below
    If Not bfound Then _
        bfound = Find_UnLocked_Cell(Cell.Row + 1, lBottom, _
                                    1, lRight, 1)

    If Not bfound Then Position_Cursor_In_Data = Failure
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Position_Cursor_In_Data - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, _
        Err.HelpContext
    Settings "Restore"      'Restore application settings
    On Error GoTo 0

End Function