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
Jase AlexanderCompliance ManagerAsked:
Who is Participating?
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.

Sam JacobsDirector of Technology Development, IPMCommented:
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

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
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 dirtCommented:
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:
HI Guys

Thank you for the response

Sam - thank you - It worked perfect

regards
Jase
Sam JacobsDirector of Technology Development, IPMCommented:
You are most welcome, Jase.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.