Mouse Click Event in VBA

Sorry, I had wrong Sub names: leftout "Before" and left out "Cancel As Boolean" in my previous comment for <Right_Click> AND <Double_Click> Events

Private Sub Worksheet_BeforeRightClick (ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B5")) Is Nothing Then
Cancel = True 'To stop standard <Right_Click> event
Range("B5").Value = 10
End If
End Sub

"He wants a value inserted in a cell when that cell is clicked on."

There would be much less possibility of unwanted side-effects by using <Right_Click> OR <Double_Click>
Private Sub Worksheet_Right_Click (ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
Cancel = True 'To stop standard <Right_Click> event
Range("B5").Value = 10
End If
End Sub

To control Various Cells with one outcome eg, .value = 10

Private Sub Worksheet_Right_Click (ByVal Target As Range) 'Or Double_Click
Dim rngControlledCells as Range
Dim rngCellToChange as Range 'Can't use Target as ByVal
Set rngControlledCells = Range("B5","C25") 'Cells or Ranges as Desired

Set rngCellToChange = Intersect(Target, rngControlledCells )
If Not rngCellToChange Is Nothing Then
Cancel = True 'To stop standard <Right_Click> event
rngCellToChange.Value = 10
End If 'Could add tidy-up set <RangeVariables to Nothing
End Sub

@Willly
I hadn't noticed those two, I must give them a try..
Merry Xmas
Alan

I was surprised to see that in this tip a question about the Mouse Click event was "solved" by using the SelectionChange event although Excel supports two mouse events:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'...
End Sub
and
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'...
End Sub

Better use one of these instead.

@S. W.
You are correct. Allen Wyatt is demonstrating so called “Event” type coding. The coding is triggered by an event, in this case a selection change. If you are “somewhere” in the worksheet and then select another place or cell , then you changed the selection , and so the coding fires. If you then continue to click on the same cell then there is no selection change: you are selecting the same place.
To get things to fire generally when you click somewhere, then some form of “Button” is typically used.
h t t p s://support.office.com/en-us/article/Overview-of-forms-Form-controls-and-ActiveX-controls-on-a-worksheet-15BA7E28-8D7F-42AB-9470-FFB9AB94E7C2
WWW.EXCELFOX.COM/FORUM/SHOWTHREAD.PHP/2241-VBA-WORKSHEET-BUTTONS-FORM-CONTROLS-COMMAND-BUTTONS-VERSES-ACTIVEX-CONTROLS-COMMAND-BUTTONS

if you click on the same cell more than once, the SelectionChange event does not fire. Unless I'm missing something...

I would-be happy When you show us About Excel 2019

How can i use this code to count clicks in different cells?

Public xRgS, xRgD As Range
Public xNum As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRgS = Range("C6")
If xRgS Is Nothing Then Exit Sub
Set xRgD = Range("N5")
If xRgD Is Nothing Then Exit Sub
If Intersect(xRgS, Target) Is Nothing Then Exit Sub
xNum = xNum + 1
xRgD.Value = xNum
End Sub

this only counts clicks in cell C6, If i copy to another cell it counts the clicks in the new cell AND the first 2, i want it to count individual clicks if thats possible

How can i use this code to count clicks in different cells?

Public xRgS, xRgD As Range
Public xNum As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRgS = Range("C6")
If xRgS Is Nothing Then Exit Sub
Set xRgD = Range("N5")
If xRgD Is Nothing Then Exit Sub
If Intersect(xRgS, Target) Is Nothing Then Exit Sub
xNum = xNum + 1
xRgD.Value = xNum
End Sub

this only counts clicks in cell C6, If i copy to another cell it counts the clicks in the new cell AND the first 2, i want it to count individual clicks if thats possible

@6StringJazzer,
Try using the: Worksheet_BeforeDoubleClick event.
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

@6StringJazzer,
Try using the: Worksheet_BeforeDoubleClick event.
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

Unfortunately this is a poor man's mouse click. A right click will also fire SelectionChange, which may not be desired.

@Sumit
Looks like your date is entered as a string using a single quote at the front (ie 'dd-mmm-yy instead of dd-mmm-yy). Remove the single quote from the cell content and make sure cell is formatted as a date value. Should work then.
Cheers

Very helpful. Experimenting with it. Trying to accomplish something similar.

Need to select a random cell, for example
A8,A8,& A8, copy there contents to G7, H7, & I7, then delete Row 8.

The kicker is I need this to happen on any cell I click.

I need to be able to click say

A21:C21 and have the contents moved to G20:I20 then row 21 deleted. This should happen on any cell I select.

This is very helpful however I would like to include two outcomes. If I include another Range("B6").Value = 11
in the statement as below, it doesn't matter which cell I click on, the second statement will happen (ie B6=11).
How can I correct this please? Thanks for your help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("B5").Address Then _
Range("B5").Value = 10
Range ("B6").Value = 11
End Sub

I want to semi-automate changing the data label on a point on an imbedded chart to "est." (estimated). I recorded a macro for doing this, but can't figure out how to trap the mouse click event to select the data point. Any previous tips on this?

@Jeff Kanoza,
The merged area's address is, always, the address of the most upper-left cell.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

I merged 9 cells together, and want to catch the mouse click in the merged area. The above code works fine for a single cell, but doesn't work once I merge them. Any help?

Thanks,
Jeff

This works:
[code snippet]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = Format(Date, "dd-mmm-yy")
End Sub
[code snippet]

Hi All,

I am facing problem regarding text format to date, in my existing report date is coming ('03-Nov-14) but i want its should be (03-Nov-14).
Please help what is the vba code for above changes?

thanks and Reards
Sumit