Add/Subtract button Excel VBA

Question

Here is an example of my project in this picture

I like collecting, so I am making an inventory sheet in Excel.

I want to make a way that I can click a button and it affects the next, or previous cell

For example:

Cell C3 would have an Add button, Cell D3 would have the number of copies I have of that item, and Cell E3 would have a Subtract button. By clicking the button in C3, it would add one more to the number in Cell D3. If you click the button in C1, it would subtract from Cell D3. Seems pretty straight forward.

Heres the conditions though:

I have like 200 items per sheet on like 20 sheets (not in the example). I would like some way that I could add the macros to all the buttons without having to make a new macro specific to each button. I also do not want it to work with the selected cell. It needs to affect the cell to the right for the add button and the cell to the left for the subtract button.

I looked everywhere for an answer, and I came pretty close to the solution.

The is the closest thing I could find but its not what i am not what i am ooking for:

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

 Sub SubtractOne()
 ActiveCell.Value = ActiveCell.Value - 1
 End Sub

I hope this makes sense and thank you all for your time.

Solution

The reason that your solution doesn't work is that the worksheet's ActiveCell value doesn't change when a user clicks on a button - they're clicking on the button, not the cell.

There's a couple of ways of tackling this. Either you can create a lot of buttons and have one generic sub to do the work, or you can trap the cell change event and respond to it. The first will be a much nicer user experience but is more work to set up initially.

Method 1: Buttons

This method uses the name of the button to perform the appropriate action, on the appropriate cell. As an overview, create all your buttons, and have each call the same generic VBA sub, which will in turn figure out which button called it, and do the appropriate operation.

Let's assume you'll call your buttons things like ADD_D3 and SUB_D3, for example to increase/decrease the value in D3.

---

First, create a subroutine to do the work in VBA:

Sub AdjustValue()
    Dim btnName As String
    Dim targetCell As String
    Dim addAmount As Integer

    btnName = Application.Caller
    targetCell = Mid(btnName, 5, Len(btnName))
    addAmount = IIf(Left(btnName, 3) = "ADD", 1, -1)

    ActiveSheet.Range(targetCell).Value = _
        ActiveSheet.Range(targetCell).Value + addAmount
End Sub

Breaking it down:

1. Application.Caller gives you the name of the caller, in this case the name of the button
2. We take the name of the target cell off the end, by skipping the first four characters
3. We figure out whether we're adding or subtracting based on the first three characters
4. We use the target cell name to update the worksheet cell

---

Next, create your buttons; add Form Control buttons to your spreadsheet for each of the + and - buttons that you need.

For each, assign it a systematic name based on the cell it should target, as described above. For example in your screenshot above you could name the first two buttons ADD_D3 and SUB_D3.
To rename a button, right-click it to select it, then in the address box in the top-left of the worksheet overwrite the name (e.g. Button 1) with your new name:

Finally, the above should work for multiple sheets too, since the sub uses ActiveSheet to access the cell.

---

Method 2: Selection Change

This is much simpler to set up, but is a bit hacky. First, set up your sheet with plus and minus symbols in each cell, coloring them however you want to make them look more button-like. For example:

Note that you have to use a single quote (') to put the symbol into the cell as text, e.g. '- and '+ (this is visible in the formula bar at the top of the example).

Then, create a single routine to respond to one of those cells being selected:

Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim numCell As Range

    If Target.Count <> 1 Then Exit Sub

    If Target.Value = "+" Then
        Set numCell = Target.Offset(0, -1)
        numCell.Select
        numCell.Value = numCell.Value + 1
    ElseIf Target.Value = "-" Then
        Set numCell = Target.Offset(0, 1)
        numCell.Select
        numCell.Value = numCell.Value - 1
    End If
End Sub

Breaking it down:

1. The name of the sub is important - it tells Excel to run your sub whenever the user clicks on any cell
2. The Target parameter is the cell that was clicked by the user,, but it could be a drag-selection too. We first check that the size is exactly 1, and exit if it isn't.
3. Next we check its value for either a + or - value. Note that we don't have to check for the quote mark.
4. We then use the Offset command to find the cell to the left or right, depending on whether we're dealing with a + or - starting cell
5. Once we have the number cell, we select it first, then change its value up or down

The reason we select the number cell is that it moves the selection off the + or - cell, so that you can click it again. If you ever want to work on those cells, you need to disable this sub temporarily, for example by putting in a Exit Sub line at the top.

Small note: Worksheet_SelectionChange is what you would use if you were working in the sheet's macro:

If you're working in the ThisWorkbook module, you'll want to use the global selection change sub:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Code goes in here
End Sub

The same code should work - and in this case will work on every sheet in your workbook.

---

Example spreadsheet showing both examples here.

Comments

[Raystafarian]

So what do you want? Something that pinpoints each cell or something that works with the active cell?

[Dave]

I have like 200 items per sheet on like 20 sheets - so, how many items do you have on how many sheets? Generally, great first question, +1

[Corkrum]

Basically Raystafarian I was given those two options. They are both not what I am looking for. I want a generic VBA Code to make buttons that will add and subtract from the middle cell. I know there is a VBA code that will add/subtract from a Specific Cell. I'll add picture maybe that will help. Sorry I am new to this site, and semi-new to Excel.

[Geoff]

No problem - did it solve the issue?

[Corkrum]

Not yet, I am still trying to figure it out. I am following both from the top of the instructions going down. Everything looks the same but the first one runs an error, and the other I am not sure how to make the cell click-able. So I have had had luck on both lol.

[Jonny Wright]

This is definitely the solution I would use (the "hacky" selection change). It means you can put your collectable name, the plus and minus anywhere you want and it will work. And you don't need to mess around with form controls.

[Geoff]

See my small edit - you may want to put your code into ThisWorkbook instead, in which case you use Workbook_SheetSelectionChange. This gives you global click-handling.