Counter button in Excel (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

Great—Thanks it works beautifully.

Reply | Quote