Trying to run code but it crashes.

D

dagda13

Board Regular
Joined
May 18, 2019
Messages
52
Hi, I recently found an excellent post by Jaafar Tribak on scrolling down ComboBox drop downs with the mouse wheel. I've tried running the following code.

This is the code attached to my ComboBox (CombBox9):

Option Explicit

Private Sub ComboBox9_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
Call SetComboBoxHook(ComboBox9)
End Sub

Private Sub ComboBox9_LostFocus()
Call RemoveComboBoxHook
End Sub

Private Sub ComboBox9_Change()
'change event not affected by the hook.
End Sub

-------------------------

And this is the code in my other module (Module2):

Option Explicit

Type POINTAPI
x As Long
y As Long
End Type

Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Type MSLLHOOKSTRUCT
pt As POINTAPI
mouseData As Long
flags As Long
time As Long
dwExtraInfo As Long
End Type

#If VBA7 Then
#If Win64 Then
Declare PtrSafe Function WindowFromPoint Lib "user32" (ByVal Point As LongPtr) As LongPtr
#Else
Declare PtrSafe Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As LongPtr
#End If
Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Declare PtrSafe Function GetParent Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
Declare PtrSafe Function GetCursorPos Lib "user32" (ByRef lpPoint As POINTAPI) As LongPtr
Declare PtrSafe Function SetFocus Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Declare PtrSafe Function IsWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal nCode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As LongPtr
Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
Declare PtrSafe Function GetClientRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As RECT) As Long
Declare PtrSafe Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" (ByVal Destination As Any, ByVal Source As Any, ByVal Length As LongPtr)
Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
Dim hwnd As LongPtr, lMouseHook As LongPtr
#Else
Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long
Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long
Declare Function GetActiveWindow Lib "user32" () As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Declare Function GetCursorPos Lib "user32" (ByRef lpPoint As POINTAPI) As Long
Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long
Declare Function IsWindow Lib "user32" (ByVal hwnd As Long) As Long
Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal nCode As Long, ByVal wParam As Long, lParam As Any) As Long
Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Declare Function GetClientRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" (ByVal Destination As Any, ByVal Source As Any, ByVal Length As Long)
Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
Dim hwnd As Long, lMouseHook As Long
#End If

Const WH_MOUSE_LL = 14
Const WM_MOUSEWHEEL = &H20A
Const HC_ACTION = 0
Const WM_LBUTTONDOWN = &H201
Const WM_LBUTTONUP = &H202
Const MK_LBUTTON = &H1
Const SM_CXVSCROLL = 2

Dim oComboBox As Object

Sub SetComboBoxHook(ByVal Control As Object)
Dim tpt As POINTAPI
Dim sBuffer As String
Dim lRet As Long

Set oComboBox = Control
RemoveComboBoxHook
GetCursorPos tpt
#If VBA7 And Win64 Then
Dim lPt As LongPtr
CopyMemory lPt, tpt, LenB(tpt)
hwnd = WindowFromPoint(lPt)
#Else
hwnd = WindowFromPoint(tpt.x, tpt.y)
#End If
sBuffer = Space(256)
lRet = GetClassName(GetParent(hwnd), sBuffer, 256)
If InStr(Left(sBuffer, lRet), "MdcPopup") Then
SetFocus hwnd
#If VBA7 Then
lMouseHook = SetWindowsHookEx(WH_MOUSE_LL, AddressOf MouseProc, Application.HinstancePtr, 0)
#Else
lMouseHook = SetWindowsHookEx(WH_MOUSE_LL, AddressOf MouseProc, Application.Hinstance, 0)
#End If
End If
End Sub

Sub RemoveComboBoxHook()
UnhookWindowsHookEx lMouseHook
End Sub


#If VBA7 Then
Function MouseProc(ByVal nCode As Long, ByVal wParam As LongPtr, lParam As MSLLHOOKSTRUCT) As LongPtr
#Else
Function MouseProc(ByVal nCode As Long, ByVal wParam As Long, lParam As MSLLHOOKSTRUCT) As Long
#End If

Dim sBuffer As String
Dim lRet As Long
Dim tRect As RECT

sBuffer = Space(256)
lRet = GetClassName(GetActiveWindow, sBuffer, 256)
If Left(sBuffer, lRet) = "wndclass_desked_gsk" Then Call RemoveComboBoxHook
If IsWindow(hwnd) = 0 Then Call RemoveComboBoxHook

If (nCode = HC_ACTION) Then
If wParam = WM_MOUSEWHEEL Then
#If VBA7 And Win64 Then
Dim lPt As LongPtr
Dim Low As Long, High As Long
Dim lParm As LongPtr
CopyMemory lPt, lParam.pt, LenB(lPt)
If WindowFromPoint(lPt) = hwnd Then
#Else
Dim Low As Integer, High As Integer
Dim lParm As Long
If WindowFromPoint(lParam.pt.x, lParam.pt.y) = hwnd Then
#End If
GetClientRect hwnd, tRect
If lParam.mouseData > 0 Then
Low = tRect.Right - (GetSystemMetrics(SM_CXVSCROLL) / 2)
High = tRect.Top + ((GetSystemMetrics(SM_CXVSCROLL) / 2) + 1)
lParm = MakeLong_32_64(Low, High)
Else
Low = tRect.Right - (GetSystemMetrics(SM_CXVSCROLL) / 2)
High = tRect.Bottom - ((GetSystemMetrics(SM_CXVSCROLL) / 2) + 1)
lParm = MakeLong_32_64(Low, High)
End If
PostMessage hwnd, WM_LBUTTONDOWN, MK_LBUTTON, lParm
PostMessage hwnd, WM_LBUTTONUP, MK_LBUTTON, lParm
End If
End If
End If

MouseProc = CallNextHookEx(lMouseHook, nCode, wParam, ByVal lParam)
End Function

#If VBA7 And Win64 Then
Function MakeLong_32_64(ByVal wLow As Long, ByVal wHigh As Long) As LongPtr
Dim retVal As LongPtr, b(3) As Byte

MoveMemory ByVal VarPtr(b(0)), ByVal VarPtr(wLow), 4
MoveMemory ByVal VarPtr(b(2)), ByVal VarPtr(wHigh), 4
MoveMemory ByVal VarPtr(retVal), ByVal VarPtr(b(0)), 8
MakeLong_32_64 = retVal
#Else
Function MakeLong_32_64(ByVal wLow As Integer, ByVal wHigh As Integer) As Long
Dim retVal As Long, b(3) As Byte

MoveMemory ByVal VarPtr(b(0)), ByVal VarPtr(wLow), 2
MoveMemory ByVal VarPtr(b(2)), ByVal VarPtr(wHigh), 2
MoveMemory ByVal VarPtr(retVal), ByVal VarPtr(b(0)), 4
MakeLong_32_64 = retVal
#End If
End Function


-----------------------------------------

The code actually works for a few seconds, but then crashes. I get the following error:

Run-time error '91':

Object variable or With block variable not set.

When I run the Debugger, the following code is highlighed in my ComboBox9 code:

Private Sub ComboBox9_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
Call SetComboBoxHook(ComboBox9)
End Sub

I've tried to find the error, but no luck.
 
Sort by date Sort by votes

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Thanks Jaafar Tribak!!!

it works but I wonder why the old code doesn't work.
can you explain ?
 
Upvote 0
sacru2red said:
Hi Jaafar Tribak, i refered Here code "Trying to run code but it crashes." dagda13 comment
it seems Like to run but didn't work at other PC (Win10, office x64)
Click to expand...

You are right. I have just tested the code in office x64 and it didn't work for scrolling down.

Here is an update that should work accross all vesrions:

Workbook Sample


1- API code in Standard Module:
VBA Code: 
Option Explicit

Private Type POINTAPI
    X As Long
    Y As Long
End Type

Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Private Type MSG
    #If VBA7 Then
        hwnd As LongPtr
        message As Long
        wParam As LongPtr
        lParam As LongPtr
        time As Long
        pt As POINTAPI
    #Else
        hwnd As Long
        message As Long
        wParam As Long
        lParam As Long
        time As Long
        pt As POINTAPI
    #End If
End Type


#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function WindowFromPoint Lib "user32" (ByVal Point As LongPtr) As LongPtr
    #Else
        Private Declare PtrSafe Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As LongPtr
    #End If
    Private Declare PtrSafe Function PeekMessage Lib "user32" Alias "PeekMessageA" (lpMsg As MSG, ByVal hwnd As LongPtr, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long
    Private Declare PtrSafe Function WaitMessage Lib "user32" () As Long
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function SetFocus Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
    Private Declare PtrSafe Function GetClientRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As RECT) As Long
    Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

#Else

    Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long
    Private Declare Function PeekMessage Lib "user32" Alias "PeekMessageA" (lpMsg As MSG, ByVal hwnd As Long, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long
    Private Declare Function WaitMessage Lib "user32" () As Long
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As Long
    Private Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Private Declare Function GetClientRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
    Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

#End If


Private Const WM_MOUSEWHEEL = &H20A
Private Const WM_LBUTTONDOWN = &H201
Private Const WM_LBUTTONUP = &H202
Private Const WHEEL_DELTA = 120
Private Const MK_LBUTTON = &H1
Private Const PM_NOREMOVE = &H0
Private Const SM_CXVSCROLL = 2

Private bEnableMouseWheel As Boolean
Private oCurCombo As Object



Public Property Let EnableMouseWheel(ByVal Combobox As Object, ByVal Enable As Boolean)
    
    Dim tMsg As MSG, tRect As RECT
    Dim sBuffer As String * 256, lRet As Long
    Dim LowWord As Long, HighWord As Long, lParm As Long
    
    Application.EnableCancelKey = xlErrorHandler
    On Error GoTo UnSelectCombo
    
    bEnableMouseWheel = Enable
    
    If Not oCurCombo Is Nothing Then
        If oCurCombo Is Combobox Then
            GoTo XitLoop
        End If
    End If
    
    Set oCurCombo = Combobox
    
    If bEnableMouseWheel Then
        Debug.Print "MouseWheel Enabled for: " & oCurCombo.Name
        Do While bEnableMouseWheel
            Call WaitMessage
            If PeekMessage(tMsg, 0, 0, 0, PM_NOREMOVE) Then
                If tMsg.message = WM_MOUSEWHEEL Then
                    #If VBA7 And Win64 Then
                        Dim lPt As LongPtr, hwnd As LongPtr
                        CopyMemory lPt, tMsg.pt, LenB(tMsg.pt)
                        hwnd = WindowFromPoint(lPt)
                        lRet = GetClassName(hwnd, sBuffer, 256)
                        If InStr(Left(sBuffer, lRet), "F3 Server") Then
                            SetFocus hwnd
                            GetClientRect hwnd, tRect
                            LowWord = tRect.Right - (GetSystemMetrics(SM_CXVSCROLL) / 2)
                            If tMsg.wParam Mod WHEEL_DELTA = 0 Then
                                HighWord = tRect.Top + ((GetSystemMetrics(SM_CXVSCROLL) / 2) + 1)
                            ElseIf tMsg.wParam Mod WHEEL_DELTA = 16 Then
                                HighWord = tRect.Bottom - ((GetSystemMetrics(SM_CXVSCROLL) / 2) + 1)
                            End If
                        End If
                    #Else
                        Dim hwnd As Long
                        hwnd = WindowFromPoint(tMsg.pt.X, tMsg.pt.Y)
                        lRet = GetClassName(hwnd, sBuffer, 256)
                        If InStr(Left(sBuffer, lRet), "F3 Server") Then
                            SetFocus hwnd
                            GetClientRect hwnd, tRect
                            LowWord = tRect.Right - (GetSystemMetrics(SM_CXVSCROLL) / 2)
                            If HiWord(CLng(tMsg.wParam)) = WHEEL_DELTA Then
                                HighWord = tRect.Top + ((GetSystemMetrics(SM_CXVSCROLL) / 2) + 1)
                            ElseIf HiWord(CLng(tMsg.wParam)) = -WHEEL_DELTA Then
                                HighWord = tRect.Bottom - ((GetSystemMetrics(SM_CXVSCROLL) / 2) + 1)
                            End If
                        End If
                    #End If
                    lParm = LowWord + HighWord * &H10000
                    PostMessage hwnd, WM_LBUTTONDOWN, MK_LBUTTON, lParm
                    PostMessage hwnd, WM_LBUTTONUP, MK_LBUTTON, lParm
                End If 'WM_MOUSEWHEEL
            End If  'PeekMessage
            
            If Left(sBuffer, GetClassName(GetActiveWindow, sBuffer, 256)) = "wndclass_desked_gsk" Then
                GoTo UnSelectCombo
            End If
            DoEvents
        Loop
    End If
    
    Exit Property
    
XitLoop:
    
    Debug.Print "MouseWheel Disabled for: " & oCurCombo.Name
    Set oCurCombo = Nothing
    Exit Property
    
UnSelectCombo:
    ActiveCell.Select

End Property


Private Function HiWord(ByVal dw As Long) As Integer
    CopyMemory HiWord, ByVal VarPtr(dw) + 2, 2
End Function




2- In the Module of the Worksheet where ComboBox1 and ComboBox2 are located:
VBA Code: 
Option Explicit

Private Sub ComboBox1_GotFocus()
    EnableMouseWheel(ComboBox1) = True
End Sub

Private Sub ComboBox1_LostFocus()
    EnableMouseWheel(ComboBox1) = False
End Sub

Private Sub ComboBox2_GotFocus()
    EnableMouseWheel(ComboBox2) = True
End Sub

Private Sub ComboBox2_LostFocus()
    EnableMouseWheel(ComboBox2) = False
End Sub
 
Upvote 0
Jaafar Tribak said:
Hi sacru2red,
Which old code are you referring to ? and what is it that doesn't work ?
Regards.
Click to expand...

yes "it didn't work for scrolling down."

i wonder why it doesn't work
it looks perfect code but .. maybe "SetWindowsHookEx" does not work

is Not Declare Win32API clearly? .. why doesn't work
 
Upvote 0
sacru2red said:
yes "it didn't work for scrolling down."

i wonder why it doesn't work
it looks perfect code but .. maybe "SetWindowsHookEx" does not work

is Not Declare Win32API clearly? .. why doesn't work
Click to expand...

Hi sacru2red

My last code doesn't use a windows hook ( SetWindowsHookEx), it uses a safer method.
Have you seen the last code in Post#15 ? Have you tried the example workbook in the link ? Doest it work ?

Regards.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jaafar Tribak
  • Question Question
KeyPress Event for Worksheet Cells (Subclassing Without Crashing)
Replies
0
Views
226
Jaafar Tribak
Jaafar Tribak
D
  • Solved
Long vs Longptr
Replies
7
Views
326
Micron
M
Jaafar Tribak
  • Question Question
Capturing the Paste Event (API Hooking - Redirection)
Replies
4
Views
769
Jaafar Tribak
Jaafar Tribak
S
  • Solved
Hide userform title bar, and locking the userform into a relative position on the application.
2
Replies
16
Views
418
Jaafar Tribak
Jaafar Tribak
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
315
Minh Tuan VN
M

Forum statistics

Threads
1,223,677
Messages
6,173,791
Members
452,534
Latest member
autodiscreet

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