Detecting SHIFT being held down when worksheet double-clicked

Requirement

I need to be able to detect, in VBA, when the SHIFT key is being held down during a worksheet double-click.

I don't want to make this specific to a workseheet.

What I have tried

I put code in the 'ThisWorkbook' object's 'Sub Worksheet_BeforeDoubleClick' event

I am using the API function [GetAsyncKeyState Lib "user32"] but am having no success.

I even commented out the use of the API code and just tried to get the double-click event to set the current target to the value of 'Now' and even that is not working.

Question

Can anyone point me in the right directoim to fulfil my requirement ?
Test-key-detect---Clean.xlsm
LVL 3
AL_XResearchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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

Open 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.
0
AL_XResearchAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NorieAnalyst Assistant Commented:
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

Open in new window

0
AL_XResearchAuthor Commented:
Rob: No sorry. Setting the time is just a quick test to see if the double-click event fired. But thanks for chipping in :)
1
Rob HensonFinance AnalystCommented:
Maybe you need to expand on what you are actually trying to achieve.
0
Martin LissOlder than dirtCommented:
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

Open 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

Open 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.
0
AL_XResearchAuthor Commented:
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

Open 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
0
Martin LissOlder than dirtCommented:
I hate so say it but you are wrong...
You are correct.
1
tuviCommented:
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)

Open 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

Open 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

Open 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.
0
AL_XResearchAuthor Commented:
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.
1
NorieAnalyst Assistant Commented:
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.
0
tuviCommented:
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

Open in new window

1
AL_XResearchAuthor Commented:
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
0
AL_XResearchAuthor Commented:
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.
0
tuviCommented:
The Alt in Windows usually make the Menu active... which is also the F10 (which initially I considered)…. The F9 does nothing in Excel...
0
AL_XResearchAuthor Commented:
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.
0
NorieAnalyst Assistant Commented:
You can, apparently as I've yet to test, disable the Research panel with this.

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

Open in new window

0
AlanConsultantCommented:
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.
0
AL_XResearchAuthor Commented:
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
1
NorieAnalyst Assistant Commented:
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.
0
AL_XResearchAuthor Commented:
Norie: Yes I know (he says head in hands).
0
NorieAnalyst Assistant Commented:
AL_XResearch

That comment was really meant in reply to the comment tuvi made that F9 doesn't do anything in Excel.:)
0
AL_XResearchAuthor Commented:
My earlier post's attachment does the trick !
Earlier comment

Thanks to all for your contributions.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.