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 processing bit.  

 

Goal:  My vision is to assign specific keys to cells ("a" corresponds with A1, "b" with A2) so that when pushing a key, the keystroke is recorded as a count instantly.  For example, if a colleague pushes "a", "a", "b", "a", the value in A1 will be 3 and the value in A2 will be 1.  The big catch, is I need each cell to update instantly based on the keystroke, and I also need the option to quickly edit a cell's count in the event of an error.

 

Initially I was working with spin buttons as I figured this would be the easiest solution.  Is there any way to change the input from a mouse click to a keystroke?  If not, is there an easy way to link the value in some cells to a keystroke?  As I mentioned before, I need the cells to update instantly, which is why the spin buttons work well - clicking once updates the count, total count, and proportion (see picture).  

 

Thank you very much!  Appreciate it!

 

Picture description:

-Total count is the total number of strokes counted (sum of the cells below)

-Proportion is number of specific keystrokes over total keystrokes

 

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