Increasing a cell value by one via macro

Topic

New Reply

Hi everyone:

I’m a complete Excel idiot so please forgive me if there is an obvious answer to my question. What I am trying to do in this spreadsheet (which I’ve attached if it helps anyone) is track the statistical information of a tennis game. My aim is to enable the coach watching the game in real time to be able to use a keystroke associated with a macro to increase the value of a cell by one. I.e. pressing CTRL+A will increase the number of aces by one in cell C3, CTRL+F increases forehand winners by one in cell C6.

I was just barely able to set up totals and percentages for all the statistical categories we’d like to track but now I’m stuck and I’m not getting anywhere with Excel’s help files in terms of increasing the value of a cell. I have some experience with Word macros, but I’m lost in this realm of tables and formulas. Can anyone help me?

Reply | Quote

Replies

Keely: The following VBA Subroutine will add one to cell C3 when executed:

Public Sub AddOne()
    Range("C3").Value = Range("C3") + 1
End Sub

Reply | Quote

Keely,

I’d be inclined to have some pushbuttons to do what you want- and then assign keyboard shortcuts to them- so that they can be used either with a mouse or with the keyboard shortcut (not relying too much on the scorer’s ability with computers).

To add a button:

.Select View, Toolbars, Control Toolboc.
.Select the icon which looks like a button, then draw the button
.Right click on the control
.Put something appropriate in the caption field
.Put a letter in the “accelerator” property. That will execute when you press “Alt” plus the key- eg, if you put “S” in the accelerator field, it will execute when you press Alt + S
.Close the properties box, and double click on the control. You will be taken into the macro editor- a subroutine with a name like “Private Sub CommandButton1_Click()” will already be set up for you. In that routine, put your code- Legare’s code is a good starting point
.When you are ready to test, click on the button in the control toolbox which looks like a blue triangular set square. The will flip you from “design” mode for your control to “execute” mode. You can change back to design mode at any time.

You can also add things like option buttons- which might be useful when scoring- eg, “Agassi serving”, “Rafter serving” (guess what’s on the TV in Australia right now!)

If you later wnat to print the page without the buttons, you can either set up a print area which does not include the buttons, or change the “PrintObject” property for the control to false.

I’ve just skimmed over things- I hope that this is a good start for you

Reply | Quote

Thanks both of you for the ideas. I will give them a shot while watching the taped coverage of the Rafter/Agassi match – during the commercials, of course! – and let you know how it goes.

Reply | Quote

Keely,

I had the same problem with something I was doing a week or two ago. The problem in general is that you can’t add 1 (or anything else) to the cell directly, to the best of my knowledge, w/o going to a macro. You get a circular reference error.

So with my little knowledge of excel macros, here’s a starting point where cell(7,1) [ie, row=7, col=A] is the cell to be increased.

Sub add1()
‘
‘ add1 Macro
‘
Dim a As Integer
a = Cells(7, 1)
Debug.Print a
a = a + 1
Cells(7, 1) = a
‘
End Sub

I know you’ll get lots of other answers too. The embellishments you’d want would probably include:
– select any cell to increase (obviously) so the code has to look for the addr of the cell currently selected (assuming that’s the one to be increased)
– probably don’t want the user to have to go to tools | macros etc to invoke the macro. A button would help and is not hard to do.

fred

Reply | Quote

Thank you so much Geoff, Legare and Fred! Unbelievably, I have been able to construct the most kick-ass stats sheet I could have imagined, complete with both buttons and keystroke shortcuts to add or subtract from the statistical category. I am over the moon! Thanks again,

Reply | Quote