Detecting SHIFT being held down when worksheet double-clicked

I've not checked your code yet but you don't appear to be using the correct event, or well, the correct name for it anyway.

This is the code stub you should be using.

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

End Sub

Select allOpen in new window

PS Checked the code, when holding down SHIFT or CONTROL the BeforeDoubleClick event doesn't appeared to get triggered, it works for ALT (VK_MENU) but for some reason the Research tab appears when you double cliick while holding down ALT.

Norie: Thanks for the quick reply.

Yes my copy-and-paste mistake. 'Workbook_SheetBeforeDoubleClick' is the event I meant.

In the code that I uploaded all I was trying to do (taking things one step at a time) is to get a double-click to set the selected cell to the return of 'Now()'. That doesn't appear to work

I have of course disabled the SHIFT detection in the workbook because it didn't do anything - so I went back to basics and even that did not work.

Any suggestions from yourself or other experts would be appreciated.

Reading between the lines, are you trying to set a cell value to the current time?

If so, there is a keyboard shortcut that can do that.

Ctrl + Colon Key - sets cell to current date

Shift + Ctrl + Colon Key - sets cell to current time

If you want the current date/time to be entered in the cell being double clicked this is all you should need.

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

    Target = Now
End Sub

Select allOpen in new window

Rob: No sorry. Setting the time is just a quick test to see if the double-click event fired. But thanks for chipping in :)

Maybe you need to expand on what you are actually trying to achieve.

You have this code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Target = Now
'
'    If blnShiftKeyPressed Then
'        MsgBox "Shift+Click"
'    Else
'        MsgBox "Click"
'    End If
'Cancel = True
End Sub

Select allOpen in new window

Target is a Range object so you have to Set it first like this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set Target = Range("A1")
Target = Now
End Sub

Select allOpen in new window

However you have your code in the ThisWorkbook module and that is not a valid place for it to be. The code I posted will work in a sheet.

Martin: Thanks for contributing. I hate so say it but you are wrong, you don't need to set it to a range object since that is passed in the sintaure of the sub. The following works perfectly now I corrected my stupid typo:

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

    Target = Now
    
End Sub

Select allOpen in new window

Rob: Ultimately I am trying to get the code in the ThisWorkbook double-click handler (remember I don't want it specific to any one sheet) to act differently depending if I hold down SHIFT, CNTRL or ALT.

SHIFT seems the least likely control key to clash with other shortcuts and the user's expections of them.

Being I hit problems when I was writing this (holding down shift blocked the double-click code) I went back to basics and then made a stupid typo

I hate so say it but you are wrong...

You are correct.

1. If you want to put the event in the workbook instead of each worksheet, then the correct Sub is:

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

Select allOpen in new window

Note the extra parameter Sh as Object

2. Your GetAsyncKeyState is wrong. This is the correct code:

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

Public Function blnShiftKeyPressed() As Boolean
    If (GetAsyncKeyState(VK_SHIFT) And &H8000) <> 0 Then
        blnShiftKeyPressed = True
    Else
        blnShiftKeyPressed = False
    End If
End Function

Select allOpen in new window

To test it using the beforerightclick event:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   If blnShiftKeyPressed Then
        MsgBox "Shift+Click"
    Else
        MsgBox "Click"
    End If
End Sub

Select allOpen in new window

3. As you see above, I have to use the Right Click event to test because the double click event will never be fired when a Shift key is pressed. In Excel (and Windows) the Shift Key and a regular mouse click has a special meaning as extended selection; that is, you select a cell first, then with a shift key down you select a different cell with a mouse, the selection will extend to that cell. So, when a shift key is used with a double click, after the first click, Excel interprets as an extended selection, the second click (of the double click) is ignored.

tuvi: Thanks for the contribution.

Yes I know the double-click event signature was wrong - as I said previously; stupid typo.

And yes I know that the use of 'GetAsyncKeyState' was not correct and needed to use a bitwise mask - I was taking things one step at a time and if the double-click even fired (which it didn't thanks to my typo) was going to print out the return value to test.

I suspected that shift+left double-click may have some hidden meaning but I don't know what because even on a clean workbook that combination does nothing observable. Shift+drag on the other hand is of course 'extend selection'.

I have taken your code and put it in a clean workbook and I am afraid it doesn't work. Yes it fires on right-click (or rather before) but it always displays 'Click' rather than 'Shift+click' when the shift button is held down (I have tried both).

I need some combination of key I can have the user hold down and the left-double click - this is just a design constant from the tool I am including the functionality in. Basically double-left-clicking already performs a custom action and I need to perform an alternate action when a modifier button is held down during the click.

When you hold down shift the double click event is not triggered, it's as simple as that.

You'll need to try something else I suppose.

Weird, on my computer the right click works. Anyway, if you want to use a double click with another key, try an F key.... Following code is for pressing down F9 key and double click:

Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Const VK_F9 = &H78

Public Function blnShiftKeyPressed() As Boolean
    If (GetAsyncKeyState(VK_F9) And &H8000) <> 0 Then
        blnShiftKeyPressed = True
    Else
        blnShiftKeyPressed = False
    End If
End Function

Select allOpen in new window

Well if I use ALT I appear to have what I need.

You just also need to suppress the popup of the 'Research' pane when you Click+Alt

I have changed the code so that a double click (ALT or not) writes the action selected to the cell.

See the attached file to test.
Detect-ALT---Double-click---Clean.xlsm

Wow - a clash of posts ! Seems 'tuvi' and 'Norie' posted almost the same time as me.

Can you think of any reason to use an F key in preference to ALT ? Not having to suppress the 'Research' pane would be one but is there another ?

I think pressing ALT to 'ALTer' the function' is more intuitive for the user but I am open to suggestions.

The Alt in Windows usually make the Menu active... which is also the F10 (which initially I considered)…. The F9 does nothing in Excel...

Well yes of course Alt is part of most accelerator keys but ALT + Clicking is not use for anything in standard Excel as far as I know.

Thanks for letting me know about F9 (always useful to know spare un-mapped keys)  but that is at the top of the keyboard and this unnatural for the user to associate with a behavior modifier.

You can, apparently as I've yet to test, disable the Research panel with this.

Application.CommandBars("Research").Enabled = False

Select allOpen in new window

Just to note that F9 is one of the more common keys to use in Excel, as it forces a recalculate if calculation is set to manual, which many high end users will do.

I'd consider using another option if you can.

Alt is also potentially problematic given it is the standard way to open menus, but you can use F10 for that too, so if I had to pick between the two, I'd go for Alt.

Alan.

Alan: You see that is what comes of working at 01:00 in the morning !! How could I think F9 does nothing in Excel ! I might as well think fire is cold !

After testing numerous times I don't think ALT is a problem because:

• you are only holding it when you double-click and there is nothing built-in for that combination
• in fact you have to hold ALT for several seconds to get the Excel accelerator keyboard tips to show and almost always this is far longer than using it in combination with the double-click
• even if the accelerator tips do popup they are automatically dismissed by my code
• as I have said before ALT sounds logical to ALTer the standard double-click behaviour

Norie: thanks but I had already included this in the sample workbook I attached to my earlier post. That is how I can suppress the 'Research' pane popup on ALT-Click

F9 is used for quite a few things in Excel - Recalculate all workbooks,  (with SHIFT) calculate active worksheet, (with CTRL) minimize the workbook and in the taskbar to expand a range.

Norie: Yes I know (he says head in hands).

AL_XResearch

That comment was really meant in reply to the comment tuvi made that F9 doesn't do anything in Excel.:)