Subclassing Mouse Left Clicks

Jaafar Tribak

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,641
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
369
huiettcm
H
A
  • Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
644
RoryA
RoryA
Jaafar Tribak
  • Question
Monitoring the Windows Shell ___ [Files and Folders activity]
Replies
3
Views
1K
Dan_W
D
S
  • Question
Referencing class variable(s) from another class
Replies
0
Views
580
simurq
S
A
  • Question
Ask VBA how to Print separate PDF
Replies
6
Views
673
ceranes
C

Forum statistics

Threads
1,215,966
Messages
6,127,974
Members
449,414
Latest member
sameri

Share this page

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