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

kindoflame

New Member
Joined
Feb 17, 2025
Messages
4
Office Version
  1. 365
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.

VBA Code:
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.
 
Welcome to the Board!

You only need one macro, that looks like this:
VBA Code:
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:
VBA Code:
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).
 
Upvote 1
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.
 
Upvote 0
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?
 
Upvote 0
1739985922855.png

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:

VBA Code:
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.
 
Upvote 0
Running this will have the same macro assigned to each button on the sheet
VBA Code:
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
VBA Code:
Sub Add_One()
    With ActiveSheet.Buttons(Application.Caller)
        .TopLeftCell.Value = .TopLeftCell.Value + 1
    End With
End Sub
 
Upvote 1
Solution

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top