Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
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