Making Many Buttons that Increase the Value of the Cell they are on by 1

[kindoflame]

I want to make a button and put it on a cell so that when I click the button that cell increases by 1. Easy enough; Developer tab -> Insert -> Button - >Place on correct cell -> Assign Button the macro below.

Sub AddTally()

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

End Sub

And it works perfectly! Exactly what I want it to do. However... I have like a hundred cell I want to put similar buttons on. Using this method, I would have to make a new macro for each button and make sure each button is paired with the right one. It is certainly possible, but it would be such a pain to do. There has to be an easier way, I just have no idea what it would be. If anyone can offer me some help, I would be very grateful.

 

[Joe4]

Welcome to the Board!

You only need one macro, that looks like this:

Sub AddTally()
    ActiveCell.Value = ActiveCell.Value + 1 
End Sub

and all buttons could reference the same macro.

Or, instead of having to create all those buttons, you could do one of the following:
1. Select the cell you want to update, and then either run the button from a Custom Tool bar icon, keyboard shortcut, or from the Macro Menu
2. Use an Event Procedure like this, and have it add one to any cell on that sheet that you double-click:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ActiveCell.Value = ActiveCell.Value + 1
End Sub

(this needs to be placed in the appropriate SHEET module in order to run automatically).

 

[kindoflame]

Thanks for the suggestions, but it didn't work how I wanted. The AddTally() script adds to whatever the active cell is, not the cell under the button. For example, if I click on cell C9 and then click the button on D5, then C9 is increased by 1 which D5 stays the same. I am sure there is a single macro that could do what I want, I just can't figure it out.

I had considered using a script like you second suggestion, but there are a lot of cells where I don't want this function to apply, so I thought buttons would be a better solution. Also, people besides me are going to be using the sheet, and I think buttons are much more intuitive for them to use.

 

[Joe4]

You originally said:

I want to make a button and put it on a cell so that when I click the button that cell increases by 1

suggesting that the button and the cell you want updated are the same cells.

I am confused by your description here:

The AddTally() script adds to whatever the active cell is, not the cell under the button. For example, if I click on cell C9 and then click the button on D5, then C9 is increased by 1 which D5 stays the same. I am sure there is a single macro that could do what I want, I just can't figure it out.

I cannot tell if you are saying this is what my code does, or this is what you want it to do.

Can you show us a screen shot of what your sheet looks like?
What is the relationship between the cells with buttons, and the cells you want updated?
Based on your screen print, can you walk us through an example of what you want to happen?

 

[kindoflame]

I clicked the button on cell A1 5 times, the button on A2 2 times, the button on B1 9 times, and the button on B2 3 times. It does not matter what order I did the button clicks or what the currently active cell is, and I could manually change the values in a cell if I wished. This functions how I want. However, the code I used to get this result was:

Sub Button1_Click()
 Range("A1").Value = Range("A1").Value + 1
End Sub
Sub Button2_Click()
 Range("A2").Value = Range("A2").Value + 1
End Sub
Sub Button3_Click()
 Range("B1").Value = Range("B1").Value + 1
End Sub
Sub Button4_Click()
 Range("B2").Value = Range("B2").Value + 1
End Sub

If I tried to replicate this for over a hundred buttons in over a hundred cells, I would need to make over a hundred macros. I tried to find code that would identify the closest cell to the pressed button and then increment that cell by 1, but got really confused trying to write code that would identify the closest cell.

 

[NoSparks]

Running this will have the same macro assigned to each button on the sheet

Sub ApplyOnActionToButtons()
    Dim btn As Button
   
    For Each btn In ActiveSheet.Buttons
        With btn
            .OnAction = "Add_One"
        End With
    Next btn
End Sub

and this will add 1 to the cell under the top left corner of the button

Sub Add_One()
    With ActiveSheet.Buttons(Application.Caller)
        .TopLeftCell.Value = .TopLeftCell.Value + 1
    End With
End Sub

 

[kindoflame]

Thanks NoSparks, that works perfectly!