Results 1 to 8 of 8

Test if SHIFT key was held when CommandButton gets clicked

This is a discussion on Test if SHIFT key was held when CommandButton gets clicked within the Excel Questions forums, part of the Question Forums category; Here is my problem: On a worksheet I set up I put a CommandButton on the top. Once that gets ...

  1. #1
    New Member
    Join Date
    Mar 2006
    Posts
    6

    Default Test if SHIFT key was held when CommandButton gets clicked

    Here is my problem:
    On a worksheet I set up I put a CommandButton on the top. Once that gets clicked it fires up a UserForm that enables the user to drill down into the displayed data in 7 different ways till Sunday

    95% of the time it is the same type of drill down that the user wants. So what I want to program is that if the user holds down SHIFT and then clicks the CommandButton, the above mentioned UserForm isn't even coming up - the "standard" drill down gets performed immediately.

    Therefore what I would like to do in the Sub CommandButton_Click is to check if the user was pressing the shift button when he clicked on the CommandButton (i.e. what is the status of the Modifier keys?). I know it should be a fairly simple call of some MS Windows Systems routine that just returns the status of the the ModifierKeys (Shift, Ctrl, Alt, etc). I have spend about an hour searching for the name of the right routine on the MS developers website, but can't seem to find the solution I need.

    I am using MS Windows XP Pro and MSExcel 2003.

    The help I need:
    • - What is the name of the routine I need to call?
      - What is the exact Declare Statement I need to use in order to be able to call this routine?
      - Outside that Declare Statement, do I need to Define any variables (I always turn Option Explicit on)


    Any help would be highly appreciated!

    Thanks,

    J.W.

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,494

    Default

    I don't think you can do it that way. Of course I could be wrong and somebody may post some wonderful code to do such a thing. I would take another route and use a custom keyboard operation with the OnKey event though. As an example, I would use something like Shift + Home to fire this event.

    Here is an example...
    In your ThisWorkbook module:
    Option Explicit

    Private Const KeyCombo As String = "+{HOME}"

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ****Application.OnKey KeyCombo, ""
    End Sub

    Private Sub Workbook_Open()
    ****Application.OnKey KeyCombo, "MySub"
    End Sub


    Then in your standard module you can have the code you want to run. Just replace the "MySub" for whatever procedure you want to run.

    Does this help?
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  3. #3
    New Member
    Join Date
    Mar 2006
    Posts
    6

    Default

    Thanks, this could be a solution.

    It is different what I had envisioned and the one concern I have is I need to teach the user a different key combination that wasn't in use before at all.


    So if anyone has a solution to my question above, I would welcome it!

    Thanks,

    J.W.

  4. #4
    New Member
    Join Date
    Mar 2006
    Posts
    6

    Default Found solution to original Problem!

    Ahh! The web is big, the web is large and full of answers. Just finding them is hard ....

    Here is the link to the page where I found the solution:http://www.informit.com/articles/art...66892&seqNum=4

    I copied the code example from the above link in module 1 of an otherwise empty test workbook. Then I designed a simple CommandButton1 on Sheet1 and assigned the following code to it:
    Code:
    Private Sub CommandButton1_Click()
        If IsKeyPressed(gksKeyboardShift) Then
            MsgBox ("Shift key was held!")
        Else
            MsgBox ("Standard, no shift key")
        End If
    End Sub
    It works beautifully.

    Just in case the above link is broken, here is a copy of the code in question:
    Code:
    Private Declare Function GetKeyState Lib "user32" _
        (ByVal vKey As Long) As Integer
    
    Private Const VK_SHIFT As Long = &H10
    Private Const VK_CONTROL As Long = &H11
    Private Const VK_MENU As Long = &H12
    Private Const VK_CAPITAL = &H14
    Private Const VK_NUMLOCK = &H90
    Private Const VK_SCROLL = &H91
    
    Public Enum GetKeyStateKeyboardCodes
     gksKeyboardShift = VK_SHIFT
     gksKeyboardCtrl = VK_CONTROL
     gksKeyboardAlt = VK_MENU
     gksKeyboardCapsLock = VK_CAPITAL
     gksKeyboardNumLock = VK_NUMLOCK
     gksKeyboardScrollLock = VK_SCROLL
    End Enum
    
    Public Function IsKeyPressed _
        (ByVal lKey As GetKeyStateKeyboardCodes) As Boolean
    
     Dim iResult As Integer
     
     iResult = GetKeyState(lKey)
    
     Select Case lKey
     Case gksKeyboardCapsLock, gksKeyboardNumLock, _
        gksKeyboardScrollLock
    
      'For the three 'toggle' keys, the 1st bit says if it's
      'on or off, so clear any other bits that might be set,
      'using a binary AND
      iResult = iResult And 1
      
     Case Else
      'For the other keys, the 16th bit says if it's down or
      'up, so clear any other bits that might be set, using a
      'binary AND
      iResult = iResult And &H8000
     End Select
    
     IsKeyPressed = (iResult <> 0)
    
    End Function
    Thanks to everyone that looked at this and tried to help.

    J.W.

  5. #5
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    Seems like you have an answer but you can actually do this without an API call.

    Presuming youve used a control from the Control Toolbox or VBA toolbar then the command button has a MouseDown event. This has an argument which indicates whether the shift key is down or not over that button.

    So what you do is use the MouseDown event to change a public boolean variable to either true or false. Then in your click event you interrogate the value of the boolean variable to know whether the shift was down at the time the click event was run. Easy peasy.

    Example...
    Code:
    Public ShiftDown As Boolean
    
    Private Sub CommandButton1_Click()
    MsgBox ShiftDown
    End Sub
    
    Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Shift = 1 Then
        ShiftDown = True
    Else
        ShiftDown = False
    End If
    End Sub

  6. #6
    New Member
    Join Date
    Mar 2006
    Posts
    6

    Default

    Thanks Parry,

    obviously you have a shorter and more elegant solution!

    Yours is the one I will use.

    Thanks again,

    J.W.

  7. #7
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,494

    Default

    I learn something new everytime I come here. Thanks Parry!
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  8. #8
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    No probs Zack.

    I actually tried the DoubleClick event for a command button first as thats another option instead of using Shift-Click. However, even though I thought I was D-clicking the single click version would always run not the Dclick so obviously you need fingers like grease lightning to get that bugger to work.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com