+ Reply to Thread
Results 1 to 7 of 7

clicking on a cell to have it add a number

  1. #1
    Bryan Bloom
    Guest

    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.

  2. #2
    L. Howard Kittle
    Guest

    Re: clicking on a cell to have it add a number

    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.




  3. #3
    Gary''s Student
    Guest

    RE: clicking on a cell to have it add a number

    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.


  4. #4
    Bryan Bloom
    Guest

    RE: clicking on a cell to have it add a number

    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.


  5. #5
    Bryan Bloom
    Guest

    Re: clicking on a cell to have it add a number

    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.

    >
    >
    >


  6. #6
    Gord Dibben
    Guest

    Re: clicking on a cell to have it add a number

    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.

    >>
    >>
    >>



  7. #7
    Bryan Bloom
    Guest

    Re: clicking on a cell to have it add a number

    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.
    > >>
    > >>
    > >>

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1