Beyond Excel: VBA and Database Manipulation

Sep 8 2010   5:49PM GMT

Back to Controlling the Cursor



Posted by: Craig Hatmaker
Tags:
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:

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
End Sub
 
 

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: