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

 

[Norie]

For a start you should replace ActiveCell.Column = 3 with Target.Column = 3.

 

[StephenCrump]

When you say that you want normal key behaviour in column 3, that implies you want OnKey to apply in all other columns? Or just column A?

At the moment it's really not clear why you are using OnKey inside Worksheet_Change, or indeed whether you even need to be using OnKey (as opposed to Worksheet_Change) at all.

Can you post your full code, i.e. so we can see what you're doing with MyEnterEvent, MyNumberEvent0 etc, and whether you ever turn OnKey off.

 

[SerenityNetworks]

Thank you. I've placed a copy of the workbook here. The workbook is used to log events, such as a vehicle passing.

Yes, OnKey can be limited to just Column-A. That would be best.

There are two selections the user can make:
1) Allow the timestamp entered in Column-B to be based upon current time or a manually entered time.
2) Have it so that once the user enters a number in Column-A it automatically enters the timestamp in Column-B and then selects Column-C where anything can be entered. When the user presses the Enter-key on the 10-key pad the last row + 1 (for either Column-A or B) is selected in Column-A.

Clear as mud?

Thanks again,
Andrew

 

[StephenCrump]

There are two selections the user can make:
1) Allow the timestamp entered in Column-B to be based upon current time or a manually entered time.
2) Have it so that once the user enters a number in Column-A it automatically enters the timestamp in Column-B and then selects Column-C where anything can be entered. When the user presses the Enter-key on the 10-key pad the last row + 1 (for either Column-A or B) is selected in Column-A.

At the moment, your code could be behaving quite unexpectedly because you're testing ActiveCell instead of Target (Norie's post #2 ). Your code also makes changes to the worksheet, which will trigger the Worksheet_Change event code again. To stop this happening, You need:

Application.EnableEvents = False 
'Any code making changes to the worksheet
Application.EnableEvents = False

But based on your description above and a quick look at your workbook, can't you replace most of your code with Worksheet_Change event code monitoring column A only, i.e. no need to monitor whether the user is pressing 0, 1, 2 etc?

Perhaps you do need Application.OnKey to monitor the Enter key. Based on your code it looks like you want to take the cursor to the next row in Column A only if the Enter key is pressed whilst in Column C, i.e. a bit like a carriage return on a typewriter?

 

[SerenityNetworks]

I don't get it. I have the following. I'm trying to constrain the OnKey so that it only triggers when the user is in Column-A, but it's not working. It's triggering no matter what column the user is in.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("Hidden").Range("C2").Value = "Running" And Target.Column = 1 Then
        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
    End If
    Application.OnKey "{ENTER}", "MyEnterEvent"         ' Enter key on 10-key pad
    Application.OnKey "{107}", "MyNumberEventPlus"      ' + key
End Sub

Sub MyNumberEvent0()
'MsgBox "in MyNumberEvent0"
    If Sheets("Hidden").Range("C2").Value = "Stopped" Then
        MsgBox "10-key keypad logging is stopped.  Please type using the alpha-keyboard.."
        End
    Else
        LR1 = Cells(Rows.Count, "A").End(xlUp).Row + 1
        LR2 = Cells(Rows.Count, "B").End(xlUp).Row + 1
        LR3 = Cells(Rows.Count, "C").End(xlUp).Row + 1
        If LR1 > LR2 Then LR = LR1 Else LR = LR2
        If LR3 > LR Then LR = LR3
        Sheets("Time Entry").Range("A" & LR).Select
        Application.EnableEvents = False
        ActiveCell.Value = "0"
        Application.EnableEvents = False
    End If
    Common
End Sub

Sub Common()
'MsgBox "in common"
    Dim val01 As Variant 'last now
    Dim val02 As Variant 'last entry
    Dim val03 As Variant 'timestamp
    Dim TimeToUse2 As String
    Application.EnableEvents = False
    If Sheets("Time Entry").Range("E1").Value = "Manually enter a starting date/time in cell F1" Then
        TimeToUse2 = "Entry"
    Else
        TimeToUse2 = "Real"
    End If
    If Sheets("Hidden").Range("C2").Value = "Stopped" Then
        Application.OnKey "{RETURN}"
        MsgBox "10-key keypad logging is stopped.  Please type using the alpha-keyboard.."
        End
        
    Else 'When I log something and it's recording then the following implements
        
        If TimeToUse2 = "Real" Then
            'Sheets("Hidden").Range("C2").Value = Now 'last entry
            val03 = Now
        Else
            val01 = Sheets("Hidden").Range("A2").Value      'last now
            val02 = Sheets("Hidden").Range("B2").Value      'last entry
            val03 = val02 + Now() - val01                   'new last entry
            Sheets("Hidden").Range("A2").Value = Now        'new last now
            Sheets("Hidden").Range("B2").Value = val03      'new last entry
        End If
        
        LR1 = Cells(Rows.Count, "A").End(xlUp).Row
        LR2 = Cells(Rows.Count, "B").End(xlUp).Row
        LR3 = Cells(Rows.Count, "C").End(xlUp).Row
        If LR1 > LR2 Then LR = LR1 Else LR = LR2
        If LR3 > LR Then LR = LR3
        Sheets("Time Entry").Range("B" & LR).Select
        ActiveCell.Value = Format(val03, "mm/dd/yy hh:mm:ss") 'puts val03 value in as timestamp

        If Sheets("Time Entry").Range("D1").Value = "Yes" Then
            ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
        Else
            ActiveCell.Offset(rowOffset:=1, columnOffset:=-1).Activate
        End If
    End If
    Application.EnableEvents = False

End Sub

 

[StephenCrump]

When you set OnKey like this:

Application.OnKey "{96}", "MyNumberEvent0"       ' 0 key

then it will persist (at Application level) until you turn it off like this:

Application.OnKey "{96}", ""       ' 0 key

Instead, you're using a Worksheet_Change event to turn it on again and again, unnecessarily, but never turning it off.

At the moment, you have a number of Subs MyNumberEvent0, MyNumberEvent1, .... MyNumberEvent9 whose function appears to be to put a 0, 1, ... or 9 in Column A, i.e. what would have happened anyway if you hadn't used OnKey and changed the selected cell?

I don't think you need to be using OnKey at all for the numeric input. If all you want to do is check that the user has entered a value in column A, then timestamp Column B and move the cursor to Column C, then all you need is Worksheet_Change code.

 

[SerenityNetworks]

I don't think you need to be using OnKey at all for the numeric input. If all you want to do is check that the user has entered a value in column A, then timestamp Column B and move the cursor to Column C, then all you need is Worksheet_Change code.

It's not simply entering any value in Column-A. I was intending to be very specific and only triggering the events if 0 through 9 on the 10-key pad were entered in Column-A.

But I will change up how the user will use the workbook to trigger on any entry into Column-A. As you say, enter anything in Column-A and it triggers entering a timestamp on the same row in Column-B then navigating to Column-C. That will be fine. Also, if the user presses the enter-key on the 10-key pad then it needs to go to the next row in Column-A (ready for the next input).

But at this point I'm just confused. Will you please help me out with a short example of how this is done? I'll keep plugging away, but I confess that I'm lost.

Thanks,
Andrew

 

[StephenCrump]

If you test the value(s) of Target in the Worksheet_Change Sub, it will tell you what the user has entered. If the user has entered 2, say, does it really matter whether this was via the numeric key or the alphanumeric key?

We can use OnKey with {ENTER} to control cursor movement if the user is in Column C. But again, does it matter that the user is using the numeric keypad ENTER, as opposed to the normal enter, i.e. {RETURN}, key? Perhaps we can test for either?

 

[SerenityNetworks]

If you test the value(s) of Target in the Worksheet_Change Sub, it will tell you what the user has entered.

This is one point I'm not following. How do I "test" the value of the Target in the Worksheet_Change Sub?

If the user has entered 2, say, does it really matter whether this was via the numeric key or the alphanumeric key?

Since I'm now limiting the action to entries in Column-A, no, it doesn't matter.

We can use OnKey with {ENTER} to control cursor movement if the user is in Column C. But again, does it matter that the user is using the numeric keypad ENTER, as opposed to the normal enter, i.e. {RETURN}, key? Perhaps we can test for either?

Either would be fine.

I'm trying to follow your comments and suggestions, but I'm still lost. This task is beyond my limited understanding. I don't follow what code I need and where I need to add it. My apologies. I do appreciate the guidance.

Andrew