Jun 17 2021 12:23 PM
Jun 17 2021 12:23 PM
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!
-Total count is the total number of strokes counted (sum of the cells below)
-Proportion is number of specific keystrokes over total keystrokes
Jun 19 2021 11:47 PM
@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