clicking on a cell to have it add a number

B

Bryan Bloom

Is there a way to click on a cell and have it put in a "1" the first time you
click, and then if you click it again have it put in a "2" and so on?

We do surveys in houses and need to count for example the number of chairs.
So, if we have a cell that says "chairs" and next to it the place to put the
number of chairs, we want to be able to click on the blank cell and have it
include the number of chairs, one for each click.

Is this possible and how would you do it?

Thanks for your help.
 
L

L. Howard Kittle

Check out the use of a SPINNER. You can go up or down, just in case you
mistook a table for a chair and have to reduce Chairs by 1. <bg>

HTH
Regards,
Howard
 
G

Gary''s Student

Double click is very easy. Copy this macro to worksheet code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Range("A1").Value = Range("A1").Value + 1
Range("A2").Select
End Sub

Double-clicking on cell A1 will cause it to auto-increment.

REMEMBER: worksheeet code
 
B

Bryan Bloom

Gary, I tried to do this, but got lost. I went to macros and tried to create
one and insert the code, it didn't seem to work for me. Could you break down
the steps and do i creat a macro for each cell, one for A1, one for A2, etc?
Thanks

Bryan
 
B

Bryan Bloom

Thanks Howard, I tried a spinner and it is really easy to use.... I want to
try and see if I can get the macro Gary explained above to work cause I am
working towards a spreadsheet that you can click on just the cell and it will
increment.

Thanks

Bryan
 
G

Gord Dibben

Bryan

I agree with Howard that a spinner gives you a chance to erase a bad number.

But if you want to use event code here is a slight variation on the code you
were given by GS.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value + 1
End If
End With
End If
ActiveCell.Offset(1, 0).Select
ws_exit:
Application.EnableEvents = True
End Sub

Change A1:A100 to whatever range you may need.

Right-click on the worksheet tab and "View Code"

Paste the above code into that module.


Gord Dibben MS Excel MVP
 
B

Bryan Bloom

Gord - it worked! You are a genius! Thanks to all of you for your help.
Ultimately I want to take this spreadsheet I am developing and hopefully use
it on a handheld.. I am not sure if that version of Excel will allow the
spreadsheet to work, but I guess I will find out.

Bryan
 
Top