Forum Discussion

lostboyz189's avatar
lostboyz189
Copper Contributor
Jun 17, 2021

Counting keystrokes instantly

Howdy, This is a long question, so here's a bit of context:  My colleagues often conduct detailed counts using an archaic mechanical counter.  I'm trying to digitize it and also speed up the data pr...
excel
Formulas and Functions
Macros and VBA
amit_bhola's avatar
amit_bhola
Iron Contributor
Jun 20, 2021

lostboyz189 , One way is as below :-

 

Insert a command button in worksheet to show a userform. Use the keypress event of the userform object to identify and process the keys. To know the ascii code corresponding to the keys, print the key in immediate window during designing.

The example file is attached, and code hint is below :-

 

This goes into the code for Sheet1 (i.e. the sheet containing the command button)

Private Sub CommandButton1_Click()
        
    UserForm1.Show vbModeless
    
End Sub

 

This goes into the userform's code :-

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    'Debug.Print KeyAscii
    
    Dim editCell As String
    
    Select Case KeyAscii
    
        Case 97, 65
            editCell = "B6"
        Case 98, 66
            editCell = "B7"
        Case 99, 67
            editCell = "B8"
        Case 100, 68
            editCell = "B9"
        Case 101, 69
            editCell = "B10"
        Case 102, 70
            editCell = "B11"
        Case 103, 71
            editCell = "B12"

    End Select

    Sheets("Sheet1").Range(editCell).Value = Sheets("Sheet1").Range(editCell).Value + 1

End Sub

 

 

Resources