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

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

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

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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 14

Sam JacobsDirector of Technology Development, IPMCommented: 2019-01-29

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 41

NorieAnalyst Assistant Commented: 2019-01-29

The only click events for a worksheet are BeforeDoubleClick and BeforeRightClick, perhaps you could use them?

LVL 57

Martin LissOlder than dirtCommented: 2019-01-29

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 ManagerAuthor Commented: 2019-01-30

HI Guys

Thank you for the response

Sam - thank you - It worked perfect

regards
Jase

Sam JacobsDirector of Technology Development, IPMCommented: 2019-01-30

You are most welcome, Jase.

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

Who is Participating?