clicking on a cell to have it add a number

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.
0
BryanBloom (13)
8/19/2006 12:20:02 AM
excel 39879 articles. 2 followers. Follow

6 Replies
355 Views

Similar Articles

[PageSpeed] 5

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

"Bryan Bloom" <BryanBloom@discussions.microsoft.com> wrote in message 
news:EB859ADD-A3BB-44B5-AEE3-F37586F2A224@microsoft.com...
> 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. 


0
lhkittle (223)
8/19/2006 1:52:46 AM
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
-- 
Gary's Student


"Bryan Bloom" wrote:

> 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.
0
GarysStudent (1572)
8/19/2006 1:53:01 AM
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

"Gary''s Student" wrote:

> 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
> -- 
> Gary's Student
> 
> 
> "Bryan Bloom" wrote:
> 
> > 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.
0
BryanBloom (13)
8/19/2006 3:56:01 PM
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

"L. Howard Kittle" wrote:

> 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
> 
> "Bryan Bloom" <BryanBloom@discussions.microsoft.com> wrote in message 
> news:EB859ADD-A3BB-44B5-AEE3-F37586F2A224@microsoft.com...
> > 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. 
> 
> 
> 
0
BryanBloom (13)
8/19/2006 3:58:01 PM
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

On Sat, 19 Aug 2006 08:58:01 -0700, Bryan Bloom
<BryanBloom@discussions.microsoft.com> wrote:

>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
>
>"L. Howard Kittle" wrote:
>
>> 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
>> 
>> "Bryan Bloom" <BryanBloom@discussions.microsoft.com> wrote in message 
>> news:EB859ADD-A3BB-44B5-AEE3-F37586F2A224@microsoft.com...
>> > 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. 
>> 
>> 
>> 

0
Gord
8/19/2006 4:35:38 PM
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

"Gord Dibben" wrote:

> 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
> 
> On Sat, 19 Aug 2006 08:58:01 -0700, Bryan Bloom
> <BryanBloom@discussions.microsoft.com> wrote:
> 
> >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
> >
> >"L. Howard Kittle" wrote:
> >
> >> 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
> >> 
> >> "Bryan Bloom" <BryanBloom@discussions.microsoft.com> wrote in message 
> >> news:EB859ADD-A3BB-44B5-AEE3-F37586F2A224@microsoft.com...
> >> > 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. 
> >> 
> >> 
> >> 
> 
> 
0
BryanBloom (13)
8/20/2006 1:24:02 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

Add an Active Directory Object to Organizational Unit !!!!
Hi everyBody , I want to add an Active Directory Object (User Acount , Group , Compturer...) to an OU by using PowerShell , and i don't know How , any help will be apreciate . thanks . use the redirusr or redircmp from microsoft. It will always go to that OU. "mamhil" <mohamedtawfik@hotmail.com> wrote in message news:BAC9EB20-A4B6-466B-96A9-587B645A18F5@microsoft.com... > Hi everyBody , I want to add an Active Directory Object (User Acount , > Group , Compturer...) to an OU by using PowerShell , and i don't know How > , any help will be ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Unable to click customizing outlook today button
Unbable to click the customising outlook today button for Outlook 2000 install in the windows xp profession machine. I have updated the office 200 patch 3. But it seem remain the same problem. http://support.microsoft.com/default.aspx?scid=kb;en-us;820575 might be helpful. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Clarence" <anonymous@discussions.microsoft.com> wrote in message news:06d101c3c939$78175620$a401280a@phx.gbl... > Unbable to click the customising outlook today button for > Outlook 200...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Add PowerPoint 2007 to Office Basic
I have Office Basic installed on my laptop and need to add PowerPoint. What is the most recommended, easiest and least expensive way to do this? ...

how many receivers i can add
i want to use outlook send 2000 thousands emails to 2000 peoples at the same time. can i put them all in the recivers and send them at one time? thank you for you help ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Roman and Arabic numbers in SEQ
Running 2003 on XP. Got a doc that has SEQ numbering. The code is: ARTICLE{seq level0\h\r0}{seq level1\h\r0} etc. to level 7 and the last entry is {seq level0\r1\*ROMAN) This displays ARTICLE I The next level down is has this code: {seq level0\c\*ROMAN}.{seq level1\r1 \*arabic} This displays as : I.1, with numbers until article five displaying as V.1, V.2, etc. I want it to display as 1.1, 1.2, etc. I tried replacing the ROMAN with arabic, but it didn't change anything. I have spent hours trying to figure this out. Using reveal formating and styles, they are no styles...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

Right clicking on a CListCtrl item
I have a TreeCtl object in a dialog box. I created an OnNMRclick.. override function to capture a right clicks. The problem I can't figure out is how to find the tree item that the user has right clicked on. Here's what I tried: void CRestoreFiles::OnNMRclickXYZ(NMHDR *pNMHDR, LRESULT *pResult) POINT CurPos; TVHITTESTINFO lpht; HTREEITEM RightClickItem; GetCursorPos(&CurPos); lpht.pt = CurPos; RightClickItem = TreeView_HitTest(pNMHDR->hwndFrom,&lpht); ... I figured that GetCursorPos would give me the position of the cursor where I had right clicke...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Auto calc YTD Numbers
Cells A1:L1 equal "Jan" thru "Dec". M1 equals "YTD". Cells A2:L2 equal numeric values. If you haven't guessed it already, I would like M2 to sum all the values A2:L2 for those months previous and including the current month (but not future months). What is the best way for going about this Here are 2 ways =SUM(OFFSET($A$2,,,,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0))) or =SUM(OFFSET($A$2,,,,MONTH(TODAY()))) hopefully your computer's clock is accurate -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjo...