# Thread: Can Excel do this (2003)

1. ## Can Excel do this (2003)

I just got a Tablet PC. I would like to use it to take stats at basketball games. It's next to impossible to keep up with entering the data using 'normal' methods. So, can an Excel spreadsheet be set up like this:

Columns would be set up for each stat, rows for each player. Can a 'spinner' or 'drop-down' box be added to each stat cell that would automatically increment by 1 by tapping the cell with the stylus? For example, is someone shot a free throw, I want to just tap the Free Throw Attempted cell for the particular player and have 1 added to that cell. Can it be done? If so, please give me a starting point to get going. I haven't done any real programming for years (and even then it was amateurish), so I'm hoping the answer doesn't lie in using VB, etc.

2. ## Re: Can Excel do this (2003)

You can get close to what you want. Lets say the stats are in cells B2:L20. Then you can insert the code below into the BeforeDoubleClick event routine for the worksheet, then each time you double click on a cell in the range B2:L20 one will be added to that cell.

<pre>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim oCell As Range
If Not Intersect(Target, ActiveSheet.Range("B2:L20")) Is Nothing Then
Application.ScreenUpdating = False
Set oCell = ActiveCell
Target.Value = Target.Value + 1
Range("A1").Select
oCell.Select
Cancel = True
Application.ScreenUpdating = True
End If
End Sub
</pre>

If your stat range is different, you will need to modify the second line above for your stat range.

The above code selects cell A1 after adding one to the cell that was double clicked. This is important since if you don't do that, you can't double click the same cell twice in a row and have the code work.

3. ## Re: Can Excel do this (2003)

I apologize for how ugly this example is, but all you need to do is use the control toolbox to put in spinners and assign Linkedcells to them through the properties (right click the spinner and choose properties while in Design mode). Might take a little time setting it up and getting it pretty, but I think it does what you want.

4. ## Re: Can Excel do this (2003)

thanks to both of you. I finished up the spinner version (I should have remembered how to do it--I made a few sheets with spinners years ago). The only problem with it so far is that if I use the stylus it tends to increment 2 or 3 times at once.

I'm going to also try the Double-click version, but I'm going to have to look in to that a bit as I've never done anything like that before.

5. ## Re: Can Excel do this (2003)

One more question: is it possible to to have more than one cell in the Cell Link section? If someone makes a field goal, I want to be able to increment the Field Goals Made AND Field Goals Attempted cells at the same time.

6. ## Re: Can Excel do this (2003)

I don't have 2003, but in 2002 only one cell ref is allowed as a cell link. Entering two refs (separated by a comma) comes up INVALID REF.

To increase two cells with one click of the spinner you will have to assign a macro, on the lines of Legare's code above.

Tony.

7. ## Re: Can Excel do this (2003)

I'm really not very good at VBA, but you can do something like this with the spinner code:

Private Sub SpinButton2_SpinUp()
Dim oVal As Integer

Range("D4").Select
oVal = ActiveCell.Value
ActiveCell.FormulaR1C1 = oval + 1

End Sub

This is programmed in the FGM spinner, so it will update the FGA as you requested. I also used the macro recorder to figure this code out, so I'm sure it's pretty poor code by most people's standards...but it seems to work nonetheless.

You would want to program something for the spin down as well, so if you get the wrong player, you can correct both easily. You would also want to put some other code in, I would think, to prevent going outside the values in the spinner (eg - if you have a minimum of zero and you spin down to below zero) so you don't get some kind of error in the middle of all the bball action.

Hope this helps.

8. ## Re: Can Excel do this (2003)

Better to do something like this. No need to select, and to do 2 ranges:

Private Sub SpinButton2_SpinUp()
Range("D4") = range("D4") + 1
Range("D8") = range("D8") + 1
End Sub

Steve

9. ## Re: Can Excel do this (2003)

Remember that you're dealing with someone who has never done an Excel Macro before (did some Word ones long ago, but I've never gotten acqauinted with the 'new' system)

This is what I end up with right now:

Private sub spinbutton4_SpinUp()
Range ("C4" = range("C4") +1
End sub

(I only need the one cell to increment in the macro, since the other one increments automatically. The FG Made cell was doubling with both Range lines included)

My problem is that when I click the spinner to decrease the FG Made (which of course would only be needed in case it got increased when it shouldn't have, but it could happen), the other cell INCREASES. How can I get the Field Goals Attempted to also decrease when I decrease the Field Goals Made? Will it take a separate macro or can I just put some lines inside the one I already have? (please be specific or it's going to be over my head)

I think the spreadsheet is going to work great except that the battery life on this tablet PC is so short I don't know if it will make it all the way through a game.

10. ## Re: Can Excel do this (2003)

First, that second line (sub goalmade() ) should not be there. You are also missing a right parenthesis in the third line.

You need a SpinDown routine as well as a SpinUp routine to do what you want. You need:

<pre>Private Sub SpinButton4_SpinDown()
Range("C4") = Range("C4") - 1
End Sub

Private Sub SpinButton4_SpinUp()
Range("C4") = Range("C4") + 1
End Sub
</pre>

11. ## Re: Can Excel do this (2003)

I suppose it has something to do with how I originally set up the macro, but if I take out the Sub Goalmade() line, I get an error saying that it can't find that macro.

Also, am I right in assuming that the SpinDown has to be a completely separate macro from SpinUP? If so, how to I assign 2 macros to the same spinner. I've tried typing both sets in the same macro, but it puts a divider line in front of the second Private line. I still can't get the SpinDown section to do anything.

What would you suggest for a beginners book on doing macros in Word and Excel? It's apparent that I'm going to have to do some studying to get stuff like this to work.

12. ## Re: Can Excel do this (2003)

You need to use the spinner from the control toolbox which has more events than the one from the FORMS toolbar. You have a spinUP and a spinDOWN event depending on which you press.

If you use forms toolbar, you would have to check the linked cell and have the macro adjust the other based on the value of the linked cell. If you go this route, you could just have the second cell that you want linked to be linked to the value in the first linked cell.

For example if you want both D4 and D5 to change when you press the spinner, you could have D4 as the linked cell, then have a formula in D5: =D4. Then when D4 changes due to the spinner, D5 will be the same value.

Steve

13. ## Re: Can Excel do this (2003)

You said that this is what you had:

<pre>Private sub spinbutton4_SpinUp()
Range ("C4" = range("C4") +1
End sub
</pre>

That code has two Sub statements and only one End Sub statement. That can not work since it will cause an error. Every Sub statement must have a End Sub statement with no intervening Sub statement.

Since you said that you assigned the macro to the button, you must have used the spinner from the Form Toolbox. That spinner does not support the SpinUp and SpinDown events, and therefore, you can not use it for what you are trying to do. You have to use the spinner from the Control Toolbox. If you put a spinner from the Control Toolbox on your spreadsheet, and then right click on the spinner and select "View Code" you will be taked to the VB module that goes with the spinner. You should have a dummy change event routine in the code window that looks like this:

<pre>Private Sub SpinButton1_Change()

End Sub
</pre>

In the upper right corner of the code window is a drop down list that shows Change. If you drop this list down, you can click on SpinDown and then on SpinUp. This will put dummy routines like the one above in the code window for SpinDown and SpinUp event routines. The code window should now contain:

<pre>Option Explicit

Private Sub SpinButton1_Change()

End Sub

Private Sub SpinButton1_SpinDown()

End Sub

Private Sub SpinButton1_SpinUp()

End Sub
</pre>

You can now put the single line of code between each of the Sub and End Sub for those two routines. The code should now look like this:

<pre>Option Explicit

Private Sub SpinButton1_Change()

End Sub

Private Sub SpinButton1_SpinDown()
Range("C4") = Range("C4") - 1
End Sub

Private Sub SpinButton1_SpinUp()
Range("C4") = Range("C4") + 1
End Sub
</pre>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•