Trapping left mouse click event.

[Saee Mane]

Hi All,

Is there a simple way for trapping left mouse click event in a specified range in excel?

Basically my requirement is when I left click on any cell specified in the given range it should mark as "TRUE" and again when I left click the same cell it shoud mark as "FALSE".

Your assistance will be highly appreciated.
Thank you in advance!

 

[jasonb75]

It would be best done with a double click. Single left click would need selection change which would only work if the cell clicked was not already active.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B2:C10")) Is Nothing Then
    Cancel = True
    Target.Value = (Target.Value = False)
End If
End Sub

 

[Saee Mane]

It would be best done with a double click. Single left click would need selection change which would only work if the cell clicked was not already active.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B2:C10")) Is Nothing Then
    Cancel = True
    Target.Value = (Target.Value = False)
End If
End Sub

Thank you Jasonb for the reply!

I have used the below function:-
Worksheet_BeforeRightClick (ByVal Target as Range, Cancel as Boolean)

I want this to happen with left mouse click. Hence I was thinking of adding trapping left mouse click event in specified range of cell.

 

[jasonb75]

Hence I was thinking of adding trapping left mouse click event in specified range of cell.

There is no such thing as a left click event, only right click or double click.

The option of a left click event was probably a deliberate omission by microsoft as it would conflict with the basic functionality of cell selection.

 

[Saee Mane]

There is no such thing as a left click event, only right click or double click.

The option of a left click event was probably a deliberate omission by microsoft as it would conflict with the basic functionality of cell selection.

Got it. I will go with Double Click.

But I have one more concern. In specified range for eg:- Range ("B2:C20") if any cell contains data validation (drop-down) then there should be no change on that particular cell when double clicked.

If there is any solution for this then it will be very helpful.

 

[Jaafar Tribak]

No such left click event as mentioned by jasonb75 but I wrote a workaround code.
Check out this : Nifty Class for Trapping Mouse Clicks on Cells

 

[Gokhan Aycan]

Another workaround would be using shapes the same size as the cells (100% transparent, No Line) if you assign the following code (all can be done programmatiically):

The macro assumes shapes are named with "Cell_CellAddress", i.e., Cell_B4

Public Sub Button_Test()
    
    Dim cCell As Range
    
    Set cCell = Range(Right(Application.Caller, Len(Application.Caller) - Len("Cell_")))
    
    If UCase(cCell.Value) = "YES" Then
        cCell.Value = "No"
    Else
        cCell.Value = "Yes"
    End If
    
    Set cCell = Nothing
    
End Sub

 

[jasonb75]

all can be done programmatiically

Does that include creating and naming the shapes?
If that had to be done manually then it would be very impractical with anything more than a handful of cells.

 

[Gokhan Aycan]

Yes, all possible. Paste the following subs in a module (in my case Module1). I assumed ActiveSheet.

With the below subs you can create shapes in 2 ways:

1. Use the run macro list (ALT + F8) to run "Create Buttons". Any selected cell will be populated with shapes.

2. Call the "Create_Buttons_From_Range" macro with Call Module1.Create_Buttons_From_Range(Your_Range_Here) in your code.
Range is optional, which in that case will be the current selection.

Either case: Modify shp.OnAction = "Module1.Button_Test"
Change "Module1" to your module name where Button_Test is located.
Change "Button_Test" accordingly (post #7)

Note: Using Option Private Module at the top of the module would hide Public Subs from the run macro list, they would still be accessible by "Module_Name.Sub_Name".

Jaafar Tribak's solutions (post #6) may look intimidating but they are extremely nice. Definitely take a look at that if somehow this won't suit you.

Public Sub Create_Buttons()

    Call Create_Buttons_From_Range(Selection)

End Sub

Public Sub Create_Buttons_From_Range(Optional ByVal rng As Range)
    
    Dim cCell As Range
    Dim shp As Shape
    
    If rng Is Nothing Then Set rng = Selection
    
    For Each cCell In rng
        
        On Error Resume Next
        Set shp = ActiveSheet.Shapes("Cell_" & Replace(cCell.Address, "$", ""))
        On Error GoTo 0
        
        If shp Is Nothing Then
            Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cCell.Left, cCell.Top, cCell.Width, cCell.Height)
            shp.Name = "Cell_" & Replace(cCell.Address, "$", "")
            shp.OnAction = "Module1.Button_Test"
            shp.Fill.Transparency = 1
            shp.Line.Visible = msoFalse
            shp.Placement = xlMoveAndSize
            Set shp = Nothing
        End If
        
    Next cCell
    
End Sub

 

[Saee Mane]

No such left click event as mentioned by jasonb75 but I wrote a workaround code.
Check out this : Nifty Class for Trapping Mouse Clicks on Cells

Thank you Jaafar Tribak for the code!

I tried the code in my project and it is exexcuting correctly. The only thing is, the code is running for the specific cell. Where as I want code to be runned for particular range of cells.

Is there any solution for this?
In addition if any cell contains data validation (drop-down) then code should not affect that cell.