Control or Shift or Alt with Double Click?

[winxpdows]

Hi All,

I'm using Application.OnDoubleClick with success. But, now I'm wondering if there is any way to capture a double click while holding another key such as Control or Alt or Shift to run a different macro as well.

Any help is GREATLY appreciated!! Thanks!

 

[Tom Urtis]

A couple things sound odd about your question. One is, the OnDoubleClick method is still supported but not used as much after version 97 with the introduction of the BeforeDoubleClick event for worksheets and the workbook.

For example, if you right-click a sheet tab, left click to select View Code, and paste the following code into your worksheet module, you can identify if the Alt key is or is not pressed when you double-click a cell on that sheet.

But that makes me wonder why you would want to run a macro with that many moving parts, keeping one hand on the mouse and another on a key...there might be a better approach to whatever the why is of your request.

Anyway, here's an example:

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Const VK_MENU = &H12
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If GetAsyncKeyState(VK_MENU) < 0 Then
MsgBox "Alt-related macro goes here.", , "Alt is pressed."
Else
MsgBox "Straight-up double-click.", , "Alt NOT pressed."
End If
End Sub

 

[Jaafar Tribak]

Hi Tom - Thanks for the code.

I tried doing the same with the Ctl and Shift keys but they didn't work. Did you manage to make those two keys work ?

 

[Tom Urtis]

Hi Jaafar - -

The DoubleClick event is more stubborn and I did not spend a lot of time figuring out a workaround for that one, but the Ctrl and Alt and Shift keys are identifiable with most other buillt-in events such as SelectionChange and BeforeRightClick, example:

In the sheet module:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Run "KeyCheck"
End Sub

In a standard module:

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Const VK_CONTROL = &H11
Private Const VK_SHIFT = &H10
Private Const VK_MENU = &H12
 
Sub KeyCheck()
 
'Ctrl
If GetAsyncKeyState(VK_CONTROL) < 0 Then
MsgBox "Control is pressed."
Else
MsgBox "Control is not pressed."
End If
 
'Shift
If GetAsyncKeyState(VK_SHIFT) < 0 Then
MsgBox "Shift is pressed."
Else
MsgBox "Shift is not pressed."
End If
 
'Alt
If GetAsyncKeyState(VK_MENU) < 0 Then
MsgBox "Alt is pressed."
Else
MsgBox "Alt is not pressed."
End If
 
End Sub

The irony is, Alt is the *last* utility key I would choose to involve in a macro trigger, as it precedes so many menu item keyboard commands.

Here are the other key codes...if anyone sees a mistake please let me know but I think it's correct.

' Virtual Keys, Standard Set
 
Public Const VK_LBUTTON = &H1 
Public Const VK_RBUTTON = &H2 
Public Const VK_CANCEL = &H3 
Public Const VK_MBUTTON = &H4 
Public Const VK_BACK = &H8 
Public Const VK_TAB = &H9 
Public Const VK_CLEAR = &HC 
Public Const VK_RETURN = &HD 
Public Const VK_SHIFT = &H10 
Public Const VK_CONTROL = &H11 
Public Const VK_MENU = &H12 
Public Const VK_PAUSE = &H13 
Public Const VK_CAPITAL = &H14 
Public Const VK_ESCAPE = &H1B 
 
Public Const VK_SPACE = &H20 
Public Const VK_PRIOR = &H21 
Public Const VK_NEXT = &H22 
Public Const VK_END = &H23 
Public Const VK_HOME = &H24 
Public Const VK_LEFT = &H25 
Public Const VK_UP = &H26 
Public Const VK_RIGHT = &H27 
Public Const VK_DOWN = &H28 
Public Const VK_SELECT = &H29 
Public Const VK_PRINT = &H2A 
Public Const VK_EXECUTE = &H2B 
Public Const VK_SNAPSHOT = &H2C 
Public Const VK_INSERT = &H2D 
Public Const VK_DELETE = &H2E 
Public Const VK_HELP = &H2F 
 
' VK_A thru VK_Z are the same as their ASCII equivalents: 'A' thru 'Z' 
' VK_0 thru VK_9 are the same as their ASCII equivalents: '0' thru '9' 
 
Public Const VK_NUMPAD0 = &H60 
Public Const VK_NUMPAD1 = &H61 
Public Const VK_NUMPAD2 = &H62 
Public Const VK_NUMPAD3 = &H63 
Public Const VK_NUMPAD4 = &H64 
Public Const VK_NUMPAD5 = &H65 
Public Const VK_NUMPAD6 = &H66 
Public Const VK_NUMPAD7 = &H67 
Public Const VK_NUMPAD8 = &H68 
Public Const VK_NUMPAD9 = &H69 
Public Const VK_MULTIPLY = &H6A 
Public Const VK_ADD = &H6B 
Public Const VK_SEPARATOR = &H6C 
Public Const VK_SUBTRACT = &H6D 
Public Const VK_DECIMAL = &H6E 
Public Const VK_DIVIDE = &H6F 
Public Const VK_F1 = &H70 
Public Const VK_F2 = &H71 
Public Const VK_F3 = &H72 
Public Const VK_F4 = &H73 
Public Const VK_F5 = &H74 
Public Const VK_F6 = &H75 
Public Const VK_F7 = &H76 
Public Const VK_F8 = &H77 
Public Const VK_F9 = &H78 
Public Const VK_F10 = &H79 
Public Const VK_F11 = &H7A 
Public Const VK_F12 = &H7B 
Public Const VK_F13 = &H7C 
Public Const VK_F14 = &H7D 
Public Const VK_F15 = &H7E 
Public Const VK_F16 = &H7F 
Public Const VK_F17 = &H80 
Public Const VK_F18 = &H81 
Public Const VK_F19 = &H82 
Public Const VK_F20 = &H83 
Public Const VK_F21 = &H84 
Public Const VK_F22 = &H85 
Public Const VK_F23 = &H86 
Public Const VK_F24 = &H87 
 
Public Const VK_NUMLOCK = &H90 
Public Const VK_SCROLL = &H91 
 
' VK_L VK_R - left and right Alt, Ctrl and Shift virtual keys. 
' Used only as parameters to GetAsyncKeyState() and GetKeyState(). 
' No other API or message will distinguish left and right keys in this way. 
 
Public Const VK_LSHIFT = &HA0 
Public Const VK_RSHIFT = &HA1 
Public Const VK_LCONTROL = &HA2 
Public Const VK_RCONTROL = &HA3 
Public Const VK_LMENU = &HA4 
Public Const VK_RMENU = &HA5 
 
Public Const VK_ATTN = &HF6 
Public Const VK_CRSEL = &HF7 
Public Const VK_EXSEL = &HF8 
Public Const VK_EREOF = &HF9 
Public Const VK_PLAY = &HFA 
Public Const VK_ZOOM = &HFB 
Public Const VK_NONAME = &HFC 
Public Const VK_PA1 = &HFD 
Public Const VK_OEM_CLEAR = &HFE

 

[Jaafar Tribak]

Hi Tom,

the Ctrl and Alt and Shift keys are identifiable with most other buillt-in events such as SelectionChange and BeforeRightClick

Yes, that's what I discovered when testing.

After spending some time on this, I think that pressing the Ctrl or Shift keys prevents the DBLCLCK event from firing so those keys must be made disabled first.One way is by using the RegisterHotKey API function and then use the GetAsyncKeyState function to check if the Ctrl key is being pressed.

The main problem with this is that the user loses the native Ctrl/Shift key functionality like Ctrl+C for copying ...etc.

example that shows how to hook the Ctrl key : ( code in Thisworkbook module)

Private Declare Function GetAsyncKeyState Lib "user32" _
(ByVal vKey As Long) As Integer

Private Declare Function RegisterHotKey Lib "user32" _
(ByVal hWnd As Long, _
ByVal id As Long, _
ByVal fsModifiers As Long, _
ByVal vk As Long) As Long
 
Private Declare Function UnregisterHotKey Lib "user32" _
(ByVal hWnd As Long, _
ByVal id As Long) As Long
 
Private Const MOD_CONTROL = &H2
Private lCtrl As Long

Private Sub Workbook_Open()

    lCtrl = 1
    Call RegisterHotKey(Application.hWnd, lCtrl, MOD_CONTROL, vbKeyControl)

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call UnregisterHotKey(Application.hWnd, lCtrl)

End Sub

Private Sub Workbook_SheetBeforeDoubleClick _
(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    Cancel = True
    
    If GetAsyncKeyState(vbKeyControl) < 0 Then
        MsgBox "Ctrl-related macro goes here.", , "Ctrl is pressed."
    Else
        MsgBox "Straight-up double-click.", , "Ctrl NOT pressed."
    End If

End Sub

 

[Tom Urtis]

Thank you Jaafar...I always enjoy the API banter.