Multiple buttons using a single macro

[kcgundy]

I'm setting up something like an inventory spreadsheet. I want to have a 5 "buttons" (rectangle shapes), located in $E$3, E4, E5, E6 and E7.

Clicking each button would add to the total in $G$3 - $G$7, corresponding to the cell the button is in. (e.g. I click the button in $E$4 and the total in cell $G$4 goes from 455 to 456.)

I would like to create a single macro and assign it to all 5 buttons, where the location of the button is recognized (either in name or location) by clicking, and would just add to the total of the adjacent cell, not all 5 adjacent cells. This HAS to be easier than I'm making it but I just can't figure it out. Thanks!

[Paul_Hossler]

Welcome to the forum. Take a minute and read the FAQs in the links in my sig below



Application.Caller is your friend

I used Form buttons and gave each a unique name and assigned the same macro to each



Option Explicit
Sub AddToTotal()
Select Case Application.Caller
Case "Add_3"
ActiveSheet.Cells(3, 7).Value = ActiveSheet.Cells(3, 7).Value + 1
Case "Add_4"
ActiveSheet.Cells(4, 7).Value = ActiveSheet.Cells(4, 7).Value + 1
Case "Add_5"
ActiveSheet.Cells(5, 7).Value = ActiveSheet.Cells(5, 7).Value + 1
Case "Add_6"
ActiveSheet.Cells(6, 7).Value = ActiveSheet.Cells(6, 7).Value + 1
Case "Add_7"
ActiveSheet.Cells(7, 7).Value = ActiveSheet.Cells(7, 7).Value + 1
End Select
End Sub



There are more general purpose ways, but this seems like the most secure

[kcgundy]

That's the one, thank you!

[rothstein]

That's the one, thank you!
You could also use this macro for your five buttons...



Sub AddToTotal()
Cells(Right(Application.Caller, 1), "G").Value = Cells(Right(Application.Caller, 1), "G").Value + 1
End Sub

[Paul_Hossler]

@rothstein -- the limitation is that the button captions have to end in the row number and be limited to a single digit.

I wasn't sure what the captions were in the OP's WB so I started with the wordier and less general purpose approach to show what I though would be a flexible approach