We help IT Professionals succeed at work.

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

77 Views
Last Modified: 2019-01-30
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

Citrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
NorieAnalyst Assistant
CERTIFIED EXPERT

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 LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
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 JacobsCitrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT

Commented:
You are most welcome, Jase.