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!

 

[Saee Mane]

Ok. I have made a couple of changes to the code so it will now enable the user to toggle the values (TRUE\FALSE) each time they left click a cell within the Range A1:A30 in Sheet1.

Cell A10 is the only cell that has Data Validation in it so, it is skipped.

Workbook example

For the sake of easy use and easy subsequent editing, I have declared two module level constants at the top of the ThisWorkbook Module where you can easily and conveniently set the target Sheet and the Target Range(s) to which you want to apply the cell click event.

Private Const TARGET_RANGE = "A1:A30"    '<== change these Consts as needed !
Private Const TARGET_SHEET = "Sheet1"     '<==

The entire code in the ThisWorkbook Module will now look like this:

Option Explicit

Private Const TARGET_RANGE = "A1:A30"    '<== change these Consts as needed !
Private Const TARGET_SHEET = "Sheet1"     '<==
  

Private Sub Workbook_Activate()
    EnableCellClickEvent = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EnableCellClickEvent = False
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    If EnableCellClickEvent Then
        If Sh Is Sheets(TARGET_SHEET) Then
            If Not Application.Intersect(Target, Range(TARGET_RANGE)) Is Nothing Then
                Cancel = True
            End If
        End If
    End If

End Sub

Private Function HasValidation(ByVal Cell As Range) As Boolean
    Dim lValType As XlDVType
    On Error Resume Next
     lValType = Cell.Validation.Type
    HasValidation = Not CBool(Err.Number)
End Function



'\\===========================
 '\\ Generic OnCellClick Pseudo-Event:
 '\============================

Private Sub OnCellClick(ByVal Target As Range)

    With Target
        If .Parent Is Sheets(TARGET_SHEET) Then
            If Not HasValidation(Target) Then
                If Not Application.Intersect(Target, Range(TARGET_RANGE)) Is Nothing And .Count = 1 Then
                    .HorizontalAlignment = xlCenter
                    .Font.Color = vbRed
                    .Value = IIf(.Text <> "TRUE", "TRUE", "FALSE")
                End If
            End If
        End If
    End With

End Sub

It's Amazing!

Thank you Jaafar Tribak. The code is fulfilling all my requirements.

But I noticed few loopholes,

1. When I copied the code from workbook sample into my project (excel file) it was working correctly. But when I closed the workbook sample suddenly the code stopped and again when I opened workbook sample the code was working. Why is this happening?

2. What is purpose of "Start Cell Click Event" and "Stop Cell Click Event"?

3. What macro name have you assigned for both the buttons?

I really want to know working of this codes. Special the one which is available in "basMod".
Can you pls explain me, not in much detail but roughly.

 

[Jaafar Tribak]

@Saee Mane

1- The code stops working when closing the workbook because that's what it is supposed to do. The cell click event is is applicable only to the workbook containing the code... If we wanted to make the cell click event globally available throughout all workbooks, we would need to place the code in an addin .

2-3 - The purpose of "Start Cell Click Event" and "Stop Cell Click Event" is obvious. One is to make the cell click event work and the other to make it stop working. The first button is assigned the "Start" SUB and the second button is assigned the "Stop" SUB.

Note that the code also starts the Cell Click Event automatically when opening\activating the workbook and stops the Cell Click Event automatically when closing. That's probably the source of your confusion.

Private Sub Workbook_Activate()
    EnableCellClickEvent = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EnableCellClickEvent = False
End Sub

The reason I added the two buttons for enabling\disabling the event was just for illustration purposes or in case the user wanted to disable the click event prematurely before the workbook is closed.

In a normal scenario, the click event would be enabled automatically upon opening the workbook and would be disabled automatically on closing . This should be achieved via the above two workbook events and therefore, we wouldn't need the two buttons.

"basMod" is the module containing all the heaving lifting. Basically, it launches a second instance of excel that is invisible behind the scenes whose purpose is to remotely monitor all the mouse clicks.

Right before a mouse click is performed, the code in the remote excel instance calls the OnCellClick event procedure located in the current excel instance and passes the cell target to it .

The OnCellClick event procedure in the current excel instance is where the user decides what to do each time a cell click is carried out. This makes the code extremely flexible and intuitive as it mimics the signature of standard events.

Now, if you ask why we run the code that monitors the mouse clicks from a second instance of excel behind the scenes, the answer is because the code uses a windows hook (WH_MOUSE_LL) which if ran from the current excel instance, it would make excel unstable at best and would crash the entire application should an unhandled error occur or a loss of state happen... Placing the mouse hook code in a second excel instance prevents all potential issues.

The "basMod" module should be left untouched . The user would only need to deal with the OnCellClick event procedure that is located in the ThisWorkbook module. Just like how we work with other excel standard events.

 

[Saee Mane]

You are great!

Thank you so much Jaafar Tribak for the solution.

 

[Jaafar Tribak]

Happy you got this working in the end and thanks for the feedback.