Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counter button in Excel (Excel 2003)

    How can I create a macro with button to easily count activities? I have multiple staff persons providing customer service in a variety of categories. I would like to create an easy way for them to click a button under the category of service provided to customers. Can this be done in Excel with a macro and command button for each category? Each staff person has a worksheet with the buttons that adds to the tally on a master sheet. Open to suggestions.Thanks.....

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counter button in Excel (Excel 2003)

    We would need a lot more info to provide a macro. COuld you provide more details and perhaps a sample sheet with proprietary info deleted.

    If I understand your setup, a non-macro solution would be to use autofilter and the subtotal function. Subtotal will not count the itmes removed in the filter, so by clicking on the picklist for the filter (and multiple ones can be selected) the count, average, min, max, etc of the filtered data can be displayed.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counter button in Excel (Excel 2003)

    If you create the command buttons from the Forms toolbar, you can write macros for them in a standard module and assign them to the buttons - when you create a new button, you are prompted to assign a macro, and later you can right-click a button and select "Assign macro" from the popup menu). Code could look like this (substitute the appropriate cell):

    Sub IncreaseActivityA()
    Range("B37") = Range("B37") + 1
    End Sub

    If you create a command button from the Control Toolbox, you are automatically switched to design mode. Double click the button to create an event procedure for the Click event in the module associated with the worksheet. Code could look like this:

    Private Sub CommandButton1_Click()
    Range("B37") = Range("B37") + 1
    End Sub

    Cells referring to the affected cell (B37 in my example) will be updated automatically.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter button in Excel (Excel 2003)

    Giving this a try right now and will let you know. Thanks....

  5. #5
    BakerMan
    Guest

    Re: Counter button in Excel (Excel 2003)

    I use data validation along with sumproduct to select --person---task---and count / and separate each

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter button in Excel (Excel 2003)

    I can get the button to add count within the same worksheet but can't figure out how to get it to go to another worksheet in the same workbook or another workbook. I can't figure out the correct syntax. Can you help me with this also? Thanks...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counter button in Excel (Excel 2003)

    Another worksheet in the same workbook:

    Worksheets("Sheet3").Range("B37") = Worksheets("Sheet3").Range("B37") + 1

    A worksheet in another workbook (which must be open):

    Workbooks("Tally.xls").Worksheets("Sheet3").Range( "B37") = Workbooks("Tally.xls").Worksheets("Sheet3").Range( "B37") + 1

    Another option would be to increase a cell in the same worksheet, as in the code I originally posted, and to place a link to that cell in another worksheet (in the same or another workbook).

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter button in Excel (Excel 2003)

    Great---Thanks it works beautifully.

Posting Permissions

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