How to use Excel to tally without pressing the enter key

[Greg Moore]

Hello,
I would like to use Excel to tally counts that I do. In an ideal setting I would place the cursor in A1 and enter multiple characters such as "///.,,./" to represent different items. I can get a running count if I press the enter key after each character click or at the end. My problem is that I want to update the tally with each key stroke so I can see when I reach 100 without pressing the enter key.

Any assistance is appreciated.

Greg

 

[StephenCrump]

Welcome to the Forum!

Perhaps something along these lines? (I have used the down arrow key as the counter)

Sub StartTally()

    Application.OnKey "{Down}", "Tally"

End Sub
Sub StopTally()

    Range("A1").ClearContents
    Application.OnKey "{Down}"

End Sub
Sub Tally()
    
    Range("A1").Value = Range("A1").Value + 1

End Sub

This can easily be expanded for simultaneous tallies of more than one item. The question wasn't completely clear on this point.

 

[Greg Moore]

It is good to be part of the forum.

Very appreciative of your help. I played around with the macro and came up with the following. It facilitates multiple simultaneous tallies and will work well as is, but I have one remaining question. Instead of the B, N, and M keys, is it possible to code for the comma, period and forward slash keys to be used? Tried to directly substitute them into the code, but didn't work.

 

[StephenCrump]

Just wrap in brackets, e.g.

Application.OnKey "{/}", "Tally"

You will probably find that "/" brings up the keyboard shortcuts menu. To stop this happening, go to the end of File/Options/Advanced and clear the box that says "Microsoft Excel menu key" (assuming you're happy not to have this functionality).

 

[Greg Moore]

Perfect !

Thanks again,
Greg