Excel VBA Incrementing the value in a specific cell by 1 when left mouse is clicked inside cell

Jase Alexander
Jase Alexander used Ask the Experts™
on
Hi Experts

I wonder if you can help

Im writing a simple stock control system and for user friendliness was wondering if there was anyway using VBA that when the mouse is left clicked in a certain cell, sat A1, the number is incremented by 1

Subsequently, if the mouse is right clicked in the cell A1, then it would decrease by 1

Is this possible?

Any advice would be welcome

J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Director of Technology Development, IPM
Commented:
I don't believe there is a left (single) click handler. There is a double-click handler though.
It will increment A1 if you double-click the left button, and decrement it if you single-click the right mouse button.
It is set to only work for A1 ... delete the 2 specified lines to work for any cell.
It also only decrements to zero .. remove the check if you want to be able to go negative.

Try this (insert into the VBA code of the worksheet):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range

' delete the line below to enable code for any cell on the worksheet
If Target.Row <> 1 Or Target.Column <> 1 Then Exit Sub

Set rng = Cells(Target.Row, Target.Column)
If rng.Value = "" Then
    rng.Value = 1
Else
    rng.Value = rng.Value + 1
End If

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range

' delete the line below to enable code for any cell on the worksheet
If Target.Row <> 1 Or Target.Column <> 1 Then Exit Sub

Set rng = Cells(Target.Row, Target.Column)
If rng.Value = "" Or Not IsNumeric(rng.Value) Then
    rng.Value = 0
Else
    If rng.Value > 0 Then
        rng.Value = rng.Value - 1
    End If
End If

End Sub

Open in new window

NorieAnalyst Assistant

Commented:
The only click events for a worksheet are BeforeDoubleClick and BeforeRightClick, perhaps you could use them?
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$A$1" Then
        Cancel = True
        Target.Value = Target.Value + 1
    End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$A$1" Then
        Cancel = True
        Target.Value  = Application.Max(0, Target.Value - 1)
    End If

End Sub

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please see my The magical floating ActiveX control article. It will show you how to temporarily float an ActiveX checkbox over a cell or cells, and that textbox will respond to a single right- or left-click.
Jase AlexanderCompliance Manager

Author

Commented:
HI Guys

Thank you for the response

Sam - thank you - It worked perfect

regards
Jase
Sam JacobsDirector of Technology Development, IPM

Commented:
You are most welcome, Jase.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial