Share via

I need an easy way to tally the results from a survey.

Anonymous
2011-12-07T11:59:40+00:00

I would like something that will count the number of times a button is clicked and display the information in an excel spreadsheet I have created.  Can anyone help?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. Anonymous
    2011-12-07T12:32:33+00:00

    The first step is to add a macro to bump the value in a cell every time the macro is called:

    Sub tally()

    Range("A1").Value = Range("A1").Value + 1

    End Sub

    The next step is to run a macro to make the button:

    Sub button_maker()

    Dim r As Range

    Set r = Selection

        ActiveSheet.Buttons.Add(94.5, 75.75, 51, 27.75).Select

        Selection.OnAction = "tally"

        ActiveSheet.Shapes(1).Select

        Selection.Characters.Text = "Bump"

    r.Select

    End Sub

    Each time the button is clicked, the value in A1 will increment.  Change A1 to suit your needs.

    Macros are very easy to install and use:

    1. ALT-F11  brings up the VBE window
    2. ALT-I

        ALT-M opens a fresh module

    1. paste the stuff in and close the VBE window

    If you save the workbook, the macro will be saved with it.

    To remove the macro:

       1. bring up the VBE window as above

       2. clear the code out

       3. close the VBE window

    To use the macro from Excel:

       1. ALT-F8

       2. Select the macro

       3. Touch RUN

    To learn more about macros in general, see:

    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Anonymous
    2011-12-07T19:49:35+00:00

    What I ended up doing was assigning the appropriate macro to each button.  Works like a charm, now.  Thanks for your help!

    0 comments No comments
  2. Anonymous
    2011-12-07T18:40:47+00:00

    You can't have more than one sub called "tally"

    You can "fix" "tally" so it knows which button was pressed and bump the assciated cell.

    0 comments No comments
  3. Anonymous
    2011-12-07T16:02:36+00:00

    Thanks Gary's Student!

    I am still having a few problems, though.  Everything worked fine when I tried only one button but when I added multiple buttons they wouldn't work.  To be specific, when I run the "button maker" macro I get the following error:

    Run-Time error '438':

    Object doesn't support this property or method.

    It gives me the options debug, end, or help.

    When I click debug it brings up the  module with the line Selection.Characters.Text = "Bump" highlighted.

    I also tried clicking End and when I click the button it says Ambiguous name detected: tally

    Thanks for all of your help so far.

    0 comments No comments