Macro to Add or Subtract- with clicking on a Cell

[Thomo4321]

Hi,

Im trying to have a macro to add or subtract, depending on which cell i click on.

I have a + in Cell A1 and a - in Cell B1.

I would like this macro to plus or minus 1 each time either of A1 or B1 is clicked in Cell D1

Cheers

 

[jmacleary]

Hi there. The code below should be put in the worksheet code are (right-click on the worksheet tab and select view code). Note that I have had to move the focus off cellA1/B1 into D1 to allow for repeat clicks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then
    Range("D1").Value = Range("D1").Value + 1
    Range("D1").Select
ElseIf Target.Address = "$B$1" Then
    Range("D1").Value = Range("D1").Value - 1
    Range("D1").Select
End If
Application.EnableEvents = True

End Sub

You may find it more elegant to place a button over A1 and another over B1 and create code for that button being pushed. These buttons could have text such as Add displayed instead of the plus sign. The advantage of this is that the focus wouldnt need to change.The code for the Add button would be:

Sub Button1_Click()
Range("D1").Value = Range("D1").Value + 1

End Sub

 

[JackDanIce]

@jmacleary has used a worksheet_SelectionChange event as it better matches your requirement, but if you accidently select either A1 or B1, it will change D1 unintentionally.

Try double-clicking either A1 or B1 to change D1 with:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    With Target
        If .Row = 1 And .Column < 3 Then
            Application.EnableEvents = False
            Cells(1, 4).Value = Cells(1, 4).Value + IIf(.Column = 1, .Column, -1)                
            Application.EnableEvents = True
        End If
    End With
    
    Cancel = True
End Sub

A double-click is more "intentional" operation by the User, however, a button approach is probably better still.

It depends on your preferences as a User, so another option to try.

 

[Thomo4321]

Thanks All,

Both of these options work great, I found the macros that @JackDanIce has made as he has mentioned double clicking is an intentional act where as the single click will cause the cell to unintentionally change the value.

Thanks again

 

[JackDanIce]

You're welcome.