VBA: Not trigger an OnKey event when in column 3

[SerenityNetworks]

I'm using the following code to capture key press events when the specified keys on the 10-key pad are pressed. It works perfectly, with one exception. When I'm in column 3 I do not want the key presses to do anything. I want normal key and cell behavior. Here is what is happening...

• I enter a 1 (from the 10-key pad) in cell A2
• The OnKey event picks up the key I pressed
• It enters a 1 in cell A2
• It skips to cell B2 and enters a date/time value
• It skips to cell C2

All this works fine. However, when I get to C2 I want normal behavior. What happens is...

• I type a 2 (from the 10-key pad) in cell C2
• The value 2 enters just fine
• I type an "a"
• The 2 is replaced with the "a". Ugh! I want the cell to now have "2a", not just "a".
• However, if I first enter an "a" then I can enter a 2 and the cell will show "2a" just as intended.
• If I type "222222" (in column 3), it looks like it is entering fine, but as soon as I type an "a" (or any non-trigger character) the 2s go away and are replaced by the "a".

I have struggled all day trying to figure out what's happening.
Any help will be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("Hidden").Range("C2").Value = "Running" Then
        If ActiveCell.Column = 3 Then
            'normal behavior
        Else
            Application.OnKey "{ENTER}", "MyEnterEvent"      ' Enter key on 10-key pad
            Application.OnKey "{96}", "MyNumberEvent0"       ' 0 key
            Application.OnKey "{97}", "MyNumberEvent1"       ' 1 key
            Application.OnKey "{98}", "MyNumberEvent2"       ' 2 key
            Application.OnKey "{99}", "MyNumberEvent3"       ' 3 key
            Application.OnKey "{100}", "MyNumberEvent4"      ' 4 key
            Application.OnKey "{101}", "MyNumberEvent5"      ' 5 key
            Application.OnKey "{102}", "MyNumberEvent6"      ' 6 key
            Application.OnKey "{103}", "MyNumberEvent7"      ' 7 key
            Application.OnKey "{104}", "MyNumberEvent8"      ' 8 key
            Application.OnKey "{105}", "MyNumberEvent9"      ' 9 key
            Application.OnKey "{107}", "MyNumberEventPlus"   ' + key
        End If
    Else
        Application.OnKey "{107}", "MyNumberEventPlus"   ' + key
    End If
End Sub

 

[StephenCrump]

How do I "test" the value of the Target in the Worksheet_Change Sub?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range
    
    For Each r In Target
        MsgBox "You have entered """ & r.Value & """ in cell " & r.Address
    Next r

End Sub

This allows for the possibility that Target contains more than one cell. If you want the code to run only if the user has changed a single cell, you can test that Target.Count=1

Distilled down, it sounds like you're trying to do two things:

1. When the user makes an entry in Column A, timestamp Column B and take the cursor to column C.

2. When the user hits either the {Enter} or {Return} keys whilst in column C, take the cursor to the next row in Column A

It looks like most of the code you've got is an attempt to fix the muddle you're in using Application.OnKey.

So I suggest we start with simple code that does just these two things, and then build any additional functionality you want onto that base.

It may be a day or two until I can post something back. Someone else may like to jump in in the meantime.

 

[SerenityNetworks]

Also, it seems that anything other than OnKey requires me to exit the cell (such as tabbing out or clicking elsewhere) before the action occurs. That won't work. An absolute requirement is that action occurs when the key is pressed in Column-A. At this point I don't care if it is alpha, numeric, or a symbol (with the exception of the + to turn on and off the functionality, and the enter-key to return to the next row in Column-A). There will be only be one character entered in Column-A, never more.

UPDATE: This post was entered after your post above. Let me digest what you posted above. Thank you!

 

[SerenityNetworks]

Yes, I'm (kind of) following now. The issue with the example you posted is what I mentioned above. I need the action to occur immediately when the key is pressed in Column-A. I can't wait for the user to exit the cell before the action occurs. How can I do that without OnKey?

 

[StephenCrump]

And Ahh! I finally see what you're trying to do. You want the user just to be keying numbers:

2
4
2
2 .... etc

and VBA taking care of all the rest, including the ENTER part.

One way to do this would be to use a TextBox for this input, and then code for the TextBox1_KeyUp event.

Perhaps put this on a simple UserForm: TextBox1 for the number - KeyUp event code puts the value (if numeric) into column A, tabs to TextBox2. User can enter a comment here, or just press Enter if no comment. Code enters any comment into column C and tabs back to TextBox1 for next number, etc.

Userform has Exit button for when user wants to to quit logging numbers.

 

[SerenityNetworks]

Hot dog! I figured it out!

I really needed the flexibility of a standard worksheet, so I didn't want to go the route of user forms. But your explanations of the application-level control of OnKey helped me figure out what was going on and determine the needed order of events. Once I had that it just took a little research to figure out some of the syntax for turning off and on the OnKey functionality as needed. The solutions may not be as elegant as possible. I imagine there are still areas where I could employ some common functions and improve the syntax, but it works.

I've placed a copy here for you to take a peek if you desire.

Thank you again for your guidance. Without it, I would never have figured this out. Reading alone and looking at snippets just wasn't cutting it for me.

Regards,
Andrew