Subclassing Mouse Left Clicks

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,775
Office Version
  1. 2016
Platform
  1. Windows
Hi,

As we know, for some stupid reason, Excel has no built-in Left Mouse Button click event that the programmer can capture in code.

Well, below is an attempt to create such an event using subclassing.

In this example , the code makes it possible for the user to mark with an "X" all the cells within the range "D6:H16" just by selecting them and left clicking the Mouse.

I beleive there must be a simpler/shorter way of doing this.

Any ideas are most welcome.

Code:


Place this code in the WorkSheet Module :


<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> TargetValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick _
(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)

    <SPAN style="color:#007F00">' Restoring initial cell value</SPAN>
    Target.Value = TargetValue
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeRightClick _
(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)

    <SPAN style="color:#007F00">' Restoring initial cell value</SPAN>
    Target.Value = TargetValue
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange _
(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

    <SPAN style="color:#00007F">Dim</SPAN> hwnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> HookResult <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#007F00">' Storing initial cell value</SPAN>
    TargetValue = Target
    <SPAN style="color:#00007F">If</SPAN> Union(Target, Range("D6:H16")).Address = _
        Range("D6:H16").Address <SPAN style="color:#00007F">Then</SPAN>
        hwnd = FindWindow("XLMAIN", Application.Caption)
        HookResult = Hook(hwnd)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


And this code in a Standard Module :


<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> SetWindowLong Lib _
"user32" Alias "SetWindowLongA" _
(<SPAN style="color:#00007F">ByVal</SPAN> hwnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> nIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal dwNewLong <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> CallWindowProc Lib _
"user32" Alias "CallWindowProcA" _
(<SPAN style="color:#00007F">ByVal</SPAN> lpPrevWndFunc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> hwnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal Msg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ByVal wParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal lParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> uMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Const</SPAN> WM_LBUTTONDOWN = 32
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Const</SPAN> GWL_WNDPROC = -4
<SPAN style="color:#00007F">Public</SPAN> ghWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Public</SPAN> lpPrevWndProc <SPAN style="color:#00007F">As</SPAN> Long
<SPAN style="color:#00007F">Public</SPAN> RC <SPAN style="color:#00007F">As</SPAN> Long

<SPAN style="color:#007F00">'Hooking the Left Mouse Click</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> Hook(hnWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

    ghWnd = hnWnd
    lpPrevWndProc = SetWindowLong _
    (ghWnd, GWL_WNDPROC, AddressOf WindowProc)
    Hook = 0
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Call back procedure</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> WindowProc _
(<SPAN style="color:#00007F">ByVal</SPAN> hw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> uMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal wParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ByVal lParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">If</SPAN> uMsg = WM_LBUTTONDOWN <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">With</SPAN> ActiveCell
        .Value = "X"
        .Characters.Font.Bold = <SPAN style="color:#00007F">True</SPAN>
        .HorizontalAlignment = xlCenter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Else</SPAN>
     WindowProc = CallWindowProc _
     (lpPrevWndProc, hw, uMsg, wParam, lParam)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
unHook

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> unHook() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

    RC = SetWindowLong(ghWnd, GWL_WNDPROC, lpPrevWndProc)
    unHook = RC
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>


Regards.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Jaafar...I tried your code and it errors out on the following
Code:
hwnd = FindWindow("XLMAIN", Application.Caption)
Questions: Is "XLMAIN" a ws or wb or other. Is "FindWindow" a function that you haven't included? I'm not quite sure what you're after with your question. You want to know when a left click has occurred in the range D6:H16 and insert an "X" in the cell(s) that have been left clicked? Dave
 
Upvote 0
Hi,

You are right, I forgot to include the FindWindow function when I copied the code from my workbook onto the Board.

The "XLMAIN " refers to the Excel application Class name.

The correct code in the Standard Module will then be as follow:


<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> FindWindow <SPAN style="color:#00007F">Lib</SPAN> "user32" _
Alias "FindWindowA" (<SPAN style="color:#00007F">ByVal</SPAN> lpClassName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _
ByVal lpWindowName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> SetWindowLong Lib _
"user32" Alias "SetWindowLongA" _
(<SPAN style="color:#00007F">ByVal</SPAN> hwnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> nIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal dwNewLong <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> CallWindowProc Lib _
"user32" Alias "CallWindowProcA" _
(<SPAN style="color:#00007F">ByVal</SPAN> lpPrevWndFunc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> hwnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal Msg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ByVal wParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal lParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> uMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Const</SPAN> WM_LBUTTONDOWN = 32
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Const</SPAN> GWL_WNDPROC = -4
<SPAN style="color:#00007F">Public</SPAN> ghWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Public</SPAN> lpPrevWndProc <SPAN style="color:#00007F">As</SPAN> Long
<SPAN style="color:#00007F">Public</SPAN> RC <SPAN style="color:#00007F">As</SPAN> Long

<SPAN style="color:#007F00">'Hooking the Left Mouse Click</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> Hook(hnWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

    ghWnd = hnWnd
    lpPrevWndProc = SetWindowLong _
    (ghWnd, GWL_WNDPROC, AddressOf WindowProc)
    Hook = 0
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Call back procedure</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> WindowProc _
(<SPAN style="color:#00007F">ByVal</SPAN> hw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> uMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
ByVal wParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ByVal lParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">If</SPAN> uMsg = WM_LBUTTONDOWN <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">With</SPAN> ActiveCell
        .Value = "X"
        .Characters.Font.Bold = <SPAN style="color:#00007F">True</SPAN>
        .HorizontalAlignment = xlCenter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Else</SPAN>
     WindowProc = CallWindowProc _
     (lpPrevWndProc, hw, uMsg, wParam, lParam)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
unHook

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> unHook() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

    RC = SetWindowLong(ghWnd, GWL_WNDPROC, lpPrevWndProc)
    unHook = RC
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>


Regards.
 
Upvote 0
That code works great now! Thanks for the fun. I don't quite understand it all yet so I may need to ask some more questions later. Your question...Are you trying to shorten your code or add more functionality to your "X"ing? I'm not sure what you're looking for? Dave
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top