VBA code to change add value in multiple cells with right click/left click function

Question

I am new to Excel and I have a worksheet with several command buttons. I want to know if I can add value to multiple cells by left clicking (as normal) and right clicking.

For example, if I left click the command button, it adds a (+1) to cell A1, but if I right click the same command button, it adds a (+1) to cell B1.

Here is the code that I have to add (+1) when I do a normal left click:

Sub Action68_Click()
    'update column BR by adding 1 to the cell value'
    Worksheets("Stats").Cells(CurrentPlayerRow, "BR").Value = Worksheets("Stats").Cells(CurrentPlayerRow, "BR").Value + 1
End Sub`

Is there anyway to use the right click button to add a (+1) to the cell BQ?

You should use a `SpinButton` instead. Alternatively, you could use WinApi calls to hook the mouse buttons. I had a problem with this sites downloads [Win32 Hooks in VB - The vbAccelerator Hook Library](http://www.vbaccelerator.com/codelib/hook/vbalhook.htm) but it seems like a good reference. You could get the `SpinButton` up and running in a few minutes. On the other hand, hooking and unhooking the mouse buttons will provide hours or possibly days of fun (and tons of experience points). — , Sep 23 '17 at 02:32

score 0 · Accepted Answer · answered Sep 23 '17 at 05:10

Instead of using a button, you can use the Event Codes to get the desired output.

The two such events will be Double Click Event and Right Click Event.

e.g.

if you Double Click in any cell in column BR starting from Row2, it will increment that cell by 1.
if you Right Click in any cell in column BR starting from Row2, it will increment the corresponding cell in column BS by 1.

If you think, you can work with this approach, place the following codes on Sheet Module and to do that, right click on the Sheet Tab --> View Code and paste the code given below into the opened code window.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim oval
If Target.Column = Range("BR1").Column And Target.Row > 1 Then
    Cancel = True
    oval = Target.Value
    If IsNumeric(oval) Then
        Target.Value = oval + 1
    End If
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim oval
If Target.Column = Range("BR1").Column And Target.Row > 1 Then
    Cancel = True
    oval = Target.Offset(0, 1).Value
    If IsNumeric(oval) Then
        Target.Offset(0, 1).Value = oval + 1
    End If
End If
End Sub

Thank you very much. This is definitely a better way to do it. I appreciate it. — S.Clancy, Sep 23 '17 at 16:10
@S.Clancy You're welcome! Glad you liked the idea. If that resolves your issue, please take a minute to accept the answer to mark your question as Solved. :) — Subodh Tiwari sktneer, Sep 23 '17 at 16:21

VBA code to change add value in multiple cells with right click/left click function

1 Answers1