VBA To Scroll Through ListBox Using Mouse Scroll Wheel
I have a userform Navi_Form that shows all the visible sheets and by double clicking any item of the listbox ListBox1 the respective sheet is activated.
I wanted to make the list scrollable so I googled for some code and the combined result looks as shown below. However I get excel to crash. Any ideas why?
If I remove the parts that relate to scrolling then there is no issue but that's not the point...
I am aware that this is built in functionality in 2013 but the code is destined for an addon to work on older versions (2007 mainly).
the following is placed in the userform
Code:
Option Explicit
Private Sub UserForm_Activate()
WheelHook Me 'For scrolling support
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
WheelUnHook 'For scrolling support
'...
End Sub
Private Sub UserForm_Deactivate()
WheelUnHook 'For scrolling support
'...
End Sub
Public Sub MouseWheel(ByVal Rotation As Long)
' To respond from MouseWheel event
' Scroll accordingly to direction
If Rotation > 0 Then
'Scroll up
If ListBox1.TopIndex > 0 Then
If ListBox1.TopIndex > 3 Then
ListBox1.TopIndex = ListBox1.TopIndex - 3
Else
ListBox1.TopIndex = 0
End If
End If
Else
'Scroll down
ListBox1.TopIndex = ListBox1.TopIndex + 3
End If
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Integer, sht As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
sht = ListBox1.List(i)
End If
Next i
On Error Resume Next
Sheets(sht).Activate
End
End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
With Me
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
End With
For Each ws In ActiveWorkbook.Worksheets
If Not ws.Name = "INDEX" And ws.Visible = True Then ListBox1.AddItem (ws.Name)
Next ws
ListBox1.Value = ActiveSheet.Name
End Sub
And I have also placed in a standard module the following
Code:
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
() '() ' (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
() '() ' (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
'To be able to scroll with mouse wheel within Userform
Private Declare Function CallWindowProc Lib "user32.dll" Alias "CallWindowProcA" ( _
ByVal lpPrevWndFunc As Long, ByVal hWnd As Long, ByVal Msg As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
Private Const GWL_WNDPROC = -4
Private Const WM_MOUSEWHEEL = &H20A
Dim LocalHwnd As Long
Dim LocalPrevWndProc As Long
Dim myForm As UserForm
Private Function WindowProc(ByVal Lwnd As Long, ByVal Lmsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
'To handle mouse events
Dim MouseKeys As Long
Dim Rotation As Long
If Lmsg = WM_MOUSEWHEEL Then
MouseKeys = wParam And 65535
Rotation = wParam / 65536
'My Form s MouseWheel function
Navi_Form.MouseWheel Rotation
End If
WindowProc = CallWindowProc(LocalPrevWndProc, Lwnd, Lmsg, wParam, lParam)
End Function
Public Sub WheelHook(PassedForm As UserForm)
'To get mouse events in userform
On Error Resume Next
Set myForm = PassedForm
LocalHwnd = FindWindow("ThunderDFrame", myForm.Caption)
LocalPrevWndProc = SetWindowLong(LocalHwnd, GWL_WNDPROC, AddressOf WindowProc)
End Sub
Public Sub WheelUnHook()
'To Release Mouse events handling
Dim WorkFlag As Long
On Error Resume Next
WorkFlag = SetWindowLong(LocalHwnd, GWL_WNDPROC, LocalPrevWndProc)
Set myForm = Nothing
End Sub
Thanks in advance for any helpful info. ;)
