Ascertaining keystroke during Worksheet_Change

[PaulMarks]

Dear Forum

Is there some code i could use to assign a variable with the value of the keystroke during the Worksheet_Change procedure?

IE if the user selects the enter key a varable is assigned with one value, If the user selects the tab key the varaible is assigned with another ect


Thank you all in advance

Paul Marks

 

[hatman]

Part of the problem is that the worksheet_change event fires after the keystroke is complete. Normally, I would suggest trapping the Key_Down or Key_Up event, but that's only available on MsForms, not on the Worksheet (or Workbook) model. Perhaps a windows API could look at the keyboard buffer and determine the latest keystroke, but aside from that, I think you might be out-scout...

 

[PaulMarks]

Dear Hatman

Thank you for spending your time looking at this I appreciate your efforts

Thank you once again


Paul Marks

 

[hatman]

You know, I have been thinking about this for a while, and it may not be quite what you are looking for, but the Undo buffer contains the last ACTION, and might be helpful for you.

I don't think anything other than directly accessing the Windows Keyboard buffer will help you discriminate between Enter and Tab (if that will even help), but this may help you in some other way... play with it and see:

UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)

 

[PaulMarks]

Dear Hatman

Thank you once again. I have tried this. It records the last string posted into the cell and not the last keystroke .

There is a method of application.onkey "{enter}" , "ProcName" but there does not appear to be a method of passing parameters on the call to the sub procedure.

Once again thank you

Paul Marks

 

[Zack Barresse]

It's a little down-n-dirty, but you could use a worksheet_change event looking at some application attributes to ascertain which direction and key was pressed. The downside of this if if you have your direction after enter as either Right or Left, as you can't ascertain if Enter or Tab was pressed. Here is the code ...



<font face=Tahoma New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> strKey <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strDir <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strProt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, tc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, tr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> varMyDirection <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ErrHandler
    c = ActiveCell.Column: r = ActiveCell.Row
    tc = Target.Column: tr = Target.Row
    varMyDirection = Application.MoveAfterReturnDirection
    <SPAN style="color:#00007F">If</SPAN> varMyDirection = -4121 <SPAN style="color:#00007F">Then</SPAN> strDir = "Down"
    <SPAN style="color:#00007F">If</SPAN> varMyDirection = -4161 <SPAN style="color:#00007F">Then</SPAN> strDir = "Right"
    <SPAN style="color:#00007F">If</SPAN> varMyDirection = -4162 <SPAN style="color:#00007F">Then</SPAN> strDir = "Up"
    <SPAN style="color:#00007F">If</SPAN> varMyDirection = -4159 <SPAN style="color:#00007F">Then</SPAN> strDir = "Left"
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> strDir
    <SPAN style="color:#00007F">Case</SPAN> "Down"
        <SPAN style="color:#00007F">If</SPAN> c = tc And r > tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Enter"
        <SPAN style="color:#00007F">If</SPAN> c = tc And r < tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Shift + Enter"
        <SPAN style="color:#00007F">If</SPAN> c > tc And r = tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Tab"
        <SPAN style="color:#00007F">If</SPAN> c < tc And r = tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Shift + Tab"
    <SPAN style="color:#00007F">Case</SPAN> "Up"
        <SPAN style="color:#00007F">If</SPAN> c = tc And r < tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Enter"
        <SPAN style="color:#00007F">If</SPAN> c = tc And r > tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Shift + Enter"
        <SPAN style="color:#00007F">If</SPAN> c < tc And r = tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Tab"
        <SPAN style="color:#00007F">If</SPAN> c > tc And r = tr <SPAN style="color:#00007F">Then</SPAN> strKey = "Shift + Tab"
    <SPAN style="color:#00007F">Case</SPAN> "Right"
        <SPAN style="color:#00007F">If</SPAN> r = tr And c > tc <SPAN style="color:#00007F">Then</SPAN> strKey = "Enter or Tab"
        <SPAN style="color:#00007F">If</SPAN> r = tr And c < tc <SPAN style="color:#00007F">Then</SPAN> strKey = "Shift + Enter or Tab"
    <SPAN style="color:#00007F">Case</SPAN> "Left"
        <SPAN style="color:#00007F">If</SPAN> r = tr And c < tc <SPAN style="color:#00007F">Then</SPAN> strKey = "Enter or Tab"
        <SPAN style="color:#00007F">If</SPAN> r = tr And c > tc <SPAN style="color:#00007F">Then</SPAN> strKey = "Shift + Enter or Tab"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">If</SPAN> c = tc And r = tr <SPAN style="color:#00007F">Then</SPAN> strKey = "(formula bar) Confirm"
    <SPAN style="color:#00007F">If</SPAN> Me.ProtectionMode = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        strProt = vbNewLine & vbNewLine & "This sheet is protected, which will" & vbNewLine & _
                  "affect the Tab key of the operation, possibly giving" & vbNewLine & _
                  "erroneous results."
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    strMsg = "The direction is " & strDir & " and the key pressed was " & strKey & "."
    <SPAN style="color:#00007F">If</SPAN> strProt <> "" <SPAN style="color:#00007F">Then</SPAN>
        strMsg = strMsg & vbNewLine & vbNewLine & strProt
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    MsgBox strMsg, vbInformation, "DIRECTIONAL INFORMATION"
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
ErrHandler:
    MsgBox "An error has occured with your application settings!" & vbNewLine & _
           "Please check your settings and ensure you have all components" & vbNewLine & _
           "installed and the application is not corrupt." & vbNewLine & vbNewLine & _
           "For more information, search:" & vbNewLine & vbNewLine & _
           "http://msdn2.microsoft.com/en-us/default.aspx", vbInformation
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



HTH


Edit: Left out two pieces of string on the Left and Right changes.

 

[PaulMarks]

Dear firefytr

Thank you for your assistance. Works a treat


With Kind Regards


Paul Marks