Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
This post returns to controlling the cursor in update spreadsheets.
As the user positions the cursor on the spreadsheet, we want to have it jump over ‘locked’ cells to the next ‘unlocked’ cell. The words ‘locked’ and ‘unlocked’ are quoted because we aren’t exactly using Excel’s notion of ‘locked’ and ‘unlocked’ cells. Excel provides the ability to prevent the cursor from entering locked cells when you protect the worksheet. Unfortunately, protecting the worksheet also prevents other things such as copy/paste if the paste range touches locked cells. Excel’s documentation says you can selectively allow some things within a protected worksheet, but my experiments with this have frustrated me and I’ve never gotten it to work satisfactorily (Maybe smarter minds than mine will contribute to the discussion and show us the way).
Without worksheet protection, Excel has no problem letting users do whatever they want to ‘locked’ cells. To work around this, we use the Worksheet_SelectionChange event to monitor cursor movements and call our Position_Curosr_In_Data function to help the user stay in ‘unlocked’/'open for entry’ cells. Position_Curosr_In_Data is also called from the Worksheet_Change event and we will cover it shortly. But for now, let’s look at the Worksheet_SelectionChange event.
Below is the code for the Worksheet_SelectionChange event. Almost all of it is consumed with figuring out which key the user pressed. This is important because we need to know which way to ‘jump’. If the user pressed the right arrow, we want to jump the the first unlocked cell to the right. This same code is in the Worksheet_Change event so I suppose it’s time I explained it.
At the heart of the code is an API called GetAsyncKeyState. GetAsyncKeyState is included in user32.dll. This Windows API tells us what the last key pressed was. Actually, it doesn’t do that. I wish it were that simple. But since groups of keys can be pressed simultaneously, such as the familiar Ctrl-Alt-Delete, the good folks at Microsoft created this API to tell you if a certain key is pressed or not. So if you want to determine if Ctrl, Alt, and Delete were pressed, you have to ask: “Is Ctrl pressed? And if so, is Alt pressed? And if so is Delete also pressed?” If you want more detail on this API, here are some good resources:
- Microsoft Developer’s Network Documentation: GetAsyncKeyState Function
- Chip Person’s: Testing Key States
- Answers.Com: GetAsyncKeyState
To use the API, we have to first declare it. I put this code at the top of modGeneral so it is available to all functions in my project.
'API Classes ' Get Key state Public Declare Function GetAsyncKeyState Lib "user32" _ (ByVal vKey As Long) As Integer
Once declared we can use it as shown in the code below. As you can see, we have to ask GetAsyncKeyState if a certain key was pressed. We pass it the key we want to know about, and it returns a 16 bit number. If the most significant bit is turned on, the key was pressed. &H8000 is the bit mask we use to determine if the most significant bit is on. &H8000 in binary form is 1000000000000000. If you “AND” it with GetAsyncKeyState‘s 16 bit number and the result is TRUE, the most significant bit is on and the key is pressed. Based on which key is pressed, we can determine which way to jump.
Here is the code for the Worksheet_SelectionChange event. It must be placed in the worksheet class. Next post will be on the Position_Curosr_In_Data function.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Purpose: Restrict the user to areas open for update
' Determine the last key pressed Dim sKey As String If GetAsyncKeyState(vbKeyTab) And &H8000 Then If GetAsyncKeyState(vbKeyShift) And &H8000 Then sKey = "ShiftTab" Else sKey = "Tab" End If ElseIf GetAsyncKeyState(vbKeyRight) And &H8000 Then sKey = "Right" ElseIf GetAsyncKeyState(vbKeyLeft) And &H8000 Then sKey = "Left" ElseIf GetAsyncKeyState(vbKeyPageUp) And &H8000 Then sKey = "PageUp" ElseIf GetAsyncKeyState(vbKeyUp) And &H8000 Then sKey = "Up" ElseIf GetAsyncKeyState(vbKeyDown) And &H8000 Then sKey = "Down" ElseIf GetAsyncKeyState(vbKeyPageDown) And &H8000 Then sKey = "PageDown" ElseIf GetAsyncKeyState(vbKeyReturn) And &H8000 Then sKey = "Return" Else sKey = "Mouse" End If Position_Cursor_In_Data Target, Range(sData), sKey