How can I make arrow keys skip over cells?

[MaverickPS]

I created some VBA code that pops up a little date picker calendar
whenever specific cells become activated. Problem is that when people
are moving around in the spreadsheet with the arrow keys and they hit
one of these cells the calendar pops up and stops them.

I want to make it so that the arrow keys skip over these cells and the
pop-up only happens when the cell is clicked with the mouse. Any ideas?

 

[MaverickPS]

I created some VBA code that pops up a little date picker calendar
whenever specific cells become activated. Problem is that when people
are moving around in the spreadsheet with the arrow keys and they hit
one of these cells the calendar pops up and stops them.

I want to make it so that the arrow keys skip over these cells and the
pop-up only happens when the cell is clicked with the mouse. Any ideas?

no ideas anyone?

 

[Tim Zych]

Assuming the worksheet_selectionchange is being used, here's one way.

In a regular module:

Declare Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Integer

Private Const VK_LEFT = &H25
Private Const VK_UP = &H26
Private Const VK_RIGHT = &H27
Private Const VK_DOWN = &H28

Public Function ArrowKeyPressed() As Boolean
If GetKeyState(VK_LEFT) < 0 Or GetKeyState(VK_RIGHT) < 0 Or _
GetKeyState(VK_UP) < 0 Or GetKeyState(VK_DOWN) < 0 Then
ArrowKeyPressed = True
Else
ArrowKeyPressed = False
End If
End Function

In the worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not ArrowKeyPressed Then
MsgBox Target.Address & " was selected without an arrow key."
End If
End Sub


I got the arrow key constants at:
http://safari.java.net/0672319330/ch09lev1sec3
so you can add more as needed (Tab, Enter, etc).

An alternative is to only test for the left-mouse button being pressed and
let the selectionchange event run only then, but I could not get that to
work (&H01). I saw a workaround but have not tried it, so you may want to
search online for some other options or include additional key detections,
creating a more robust function. This is just an example.