Validate a macro execution only if a keyboard key is press

[mtheriault2000]

Hello

I got a macro that is linked to a button on my sheet. I need a way to prevent false execution. I want to execute the macro only if the "CTRL" key is press at the same time that I click on the button.

I use Excel 2007 and the button are created using Excel < Insert < Illustration < rectangular . The new drawing is then linked to a macro.

Any help appreciated

Martin

 

[JackDanIce]

n/a - double posted weirdly... see next post

 

[JackDanIce]

Not sure how to do that, but why not put the following near the start of the macro that is executed from clicking the button:

Dim iCheck as String
iCheck = MsgBox("Do you wish to execute macro " & macroname & "?", vbYesNo)
If iCheck = vbNo Then Exit Sub

Or along similar lines,

Dim iCheck as String
iCheck = MsgBox("Do you wish to execute macro " & macroname & "?", vbYesCancel)
If iCheck = vbCancel Then Exit Sub

 

[mtheriault2000]

Hello & Thanks JackDanIce

The purpose is to create a form that will sent a trading order to an external application. I want a fast and secure way to operate.

That's why, I don't want to use the msgbox validation. Pressing a key while clicking on a button should be fast and safe for my purpose.

Martin

 

[Comfy]

You could remove the command button altogether and run it just on a key combination like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey "+^{RIGHT}", "MyMacro"
End Sub

So here ctrl+shift+The Right Arrow will execute the macro named MyMacro

 

[Comfy]

Had a little play and found this.

It seems to work:

Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 16
            MsgBox "You pressed Shift"
        Case 17
            MsgBox "You pressed Control"
        Case Else
            MsgBox "The KeyCode number of the key you pressed is: " & KeyCode
    End Select
End Sub

Only works once the commandbutton has focus..... meh

 

[mtheriault2000]

Had a little play and found this.

It seems to work:

Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 16
            MsgBox "You pressed Shift"
        Case 17
            MsgBox "You pressed Control"
        Case Else
            MsgBox "The KeyCode number of the key you pressed is: " & KeyCode
    End Select
End Sub

Only works once the commandbutton has focus..... meh

Thanks Comfy
Sound a good start. I will try to work around that

Martin

 

[mtheriault2000]

Resolve: Validate a macro execution only if a keyboard key is press

Did resolve the problem using GetasynchKeyState function

Private Declare Function GetAsyncKeyState Lib _
    "user32" (ByVal vKey As Long) As Integer
Public Function ShiftKey(bool)
    If CBool(GetAsyncKeyState(&H10)) Then
        ShiftKey = True
        'MsgBox "Shift key is pressed"
    Else
        ShiftKey = False
    End If
End Function

Sub Close_at_Market()
    If ShiftKey(True) Then
       Sheets("Data_In_Out").Range("o_Order_Type") = "Close at Market"
       Sheets("Data_In_Out").Range("o_New_Value") = "Yes"
       Range("ActionPerform") = "Done"
    Else
       Range("ActionPerform") = "No action Perform"
    End If
End Sub

 

[Comfy]

Very Nice