Forum Discussion
lostboyz189
Jun 17, 2021Copper Contributor
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
- jammar001Copper Contributor
https://answers.microsoft.com/en-us/msoffice/forum/all/window-handler-in-excel-vba/ccdee036-1755-4589-a79a-94f7b899b3f6 I've used this discussion and the active code by Chip Pearson, to create a loop with a delay of 0.3 second. It logs every active window in Excel and I have performance data in real time on the Freeze Pane. The "Macro L" which is on LinkedIn, I am updating the code now, and will repost on LinkedIn, soon.
In relation to this post about key logging but counting per minute - I have Logged On Active Windows Minute Total for that day of work, and a Logged On No Active Windows Minute Total. This was useful when I was doing a very automated work stream.
But now I am doing work on one document for a while. So I am looking to add to the in Active Window Minutes. I intend to add code to seperately count key presses and mouse movements per minute. To get a true Logged On Active Time, I really need to know how many minutes there were no Active Window changes & No Key Presses & No Mouse moves.
So I am looking for some code to count key presses and mouse moves and add it into my Macro L loop. For interest the Macro L saves to a C Drive folder at Lock Screen when going on a break.
- amit_bholaIron Contributor
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