Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Detect if the Shift, Ctrl, Alt or CMD key has been pressed

In Excel for Windows you can use the GetKeyState API to check this but on a Mac it is not so easy. below you find examples for Office for Windows and for Office for the Mac. Code like this can be very useful to run the code you want depending of the key status.

 

Example for Office for Windows

Excel for Windows example below for the Shift key, on top of a normal module add this :

Public Const SHIFT_KEY = &H10    'Shift

#If VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#End If

In your macro you can use this then :

If GetKeyState(SHIFT_KEY) < 0 Then MsgBox "Pressed"
 

Example 1 for Office for the Mac 2011 and 2016

Thanks to DJ Bazzie Wazzie from the script forum i was able to create this VBA example. Note: it is not very fast but if you use Mavericks (10.9 or higher) and Office 2011 you can use the second example on this page if the speed is important.

You can run the macro named TestKey below to test it, do not forget to copy the function KeyPressedCheck inside the same module because it is called by the macro. In the function call in the macro you see that I use argument 1 for testing the Shift key now, see the comments in the macro for using argument 2, 3 or 4.

Note: When you call the function from a userform button or button on a worksheet the Ctrl key test is not working, when you run the Ctrl test from the macro dialog it is working. Reason is that Ctrl + click is right click on a Mac so the Ctrl test is only working from the macro dialog.

Sub TestKey()
'1 = Shift.
'2 = Ctrl
'3 = Alt or Option
'4 = Command or CMD
    If KeyPressedCheck(1) = True Then
        MsgBox "Shift Key is pressed"
    Else
        MsgBox "Shift Key is not pressed"
    End If
End Sub


Function KeyPressedCheck(KeyToCheck As Long) As Boolean
'Ron de Bruin 11-Aug-2014, working in OS X v10.0 and up.
'Thanks to DJ Bazzie Wazzie from the macscripter forum
    Dim KeyConstant As String
    Dim ScriptToRun As String

    Select Case KeyToCheck
    Case 1: KeyConstant = "NSShiftKeyMask"    'Shift key
    Case 2: KeyConstant = "NSControlKeyMask"  'Ctrl key
    Case 3: KeyConstant = "NSAlternateKeyMask"  'Alt or Option key
    Case 4: KeyConstant = "NSCommandKeyMask"  'Command or CMD key
    End Select

    ScriptToRun = "do shell script ""/usr/bin/python -c 'import Cocoa; " & _
        "print Cocoa.NSEvent.modifierFlags() & Cocoa." & KeyConstant & " > 1'"""

    On Error Resume Next
    KeyPressedCheck = MacScript(ScriptToRun)
    On Error GoTo 0
End Function

 

Example 2 for Office for the Mac 2011

Note : This example is not working in Excel 2016, example 1 is also working in Excel 2016

Thanks to Shane Stanley from the script forum I was able to create this VBA example. Note: you must run Mavericks (10.9 or up) on your Mac if you want to use this example. This example is much faster then the first example on this page but you need the extra script bundle file (checkModifier.scptd).

Download Excel example file and the script bundle file

When you want to test the code in the Excel file the script bundle file (checkModifier.scptd) must be in the same folder of the workbook or add-in because the VBA code call this script file to get the info we need. You can change the vba code if you want to place the script bundle file (checkModifier.scptd) into another folder on your Mac.

This is the VBA code that you see in the Excel workbook and it will work if the the script bundle file (checkModifier.scptd) is in the same folder. Important: do not change anything in the script bundle file.

Sub TestKey()
'Ron de Bruin 21-Aug-2014, working in OS X v10.9 and up.
'Thanks to Shane Stanley from the macscripter forum
Dim TestKeyScriptToRun As String
Dim Answer As Long

TestKeyScriptToRun = "set TestKeyScript to """ & ThisWorkbook.Path & _
    Application.PathSeparator & "checkModifier.scptd"" as alias"
TestKeyScriptToRun = TestKeyScriptToRun & Chr(13) & "run script TestKeyScript"

On Error Resume Next
    Answer = MacScript(TestKeyScriptToRun)
On Error GoTo 0

Select Case Answer
    Case 0: MsgBox "Run code when no key is pressed"
    Case 1: MsgBox "Run code when Shift key is pressed"
    Case 2: MsgBox "Run code when Ctrl key is pressed"
    Case 3: MsgBox "Run code when Alt or Option key is pressed"
    Case 4: MsgBox "Run code when Command or CMD key is pressed"
End Select
    
End Sub

Note: When you call the function from a userform button or button on a worksheet the Ctrl key test is not working, when you run the Ctrl test from the macro dialog it is working. Reason is that Ctrl + click is right click on a Mac so the Ctrl test is only working from the macro dialog.

If you have suggestions or have problems please let me know.