USing Scroll Wheel to scroll through userform

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
165
Hi,

I found the following piece of code to scroll through a userform (Not listboxes or comboboxes but the length of the actual form).
I have a multipage userform, when i use the code it allows me to scroll using the mouse wheel however wen it scrolls its just shows a grey area as if there was no content below what was visible on the screen when the userform was opened (hope that makes sense).

Can anyone help sort the problem out?
Perhaps i need to state the length of the form I want to scroll or something?.. I've honestly no idea but i'm hoping some of the excel wizards on here will know of a solution.

Many thanks in advance for your help,

Mike
PS
just ignore the *'s, they're not part of the code

VBA Code:
[LIST=1]
[*]Option Explicit
[*]' Based on code from Peter Thornton here:
[*]' http://social.msdn.microsoft.com/Forums/en-US/7d584120-a929-4e7c-9ec2-9998ac639bea/mouse-scroll-in-userform-listbox-in-excel-2010?forum=isvvba
[*]Private Type POINTAPI
[*]x As Long
[*]y As Long
[*]End Type
[*]Private Type MOUSEHOOKSTRUCT
[*]pt As POINTAPI
[*]hwnd As Long
[*]wHitTestCode As Long
[*]dwExtraInfo As Long
[*]End Type
[*]Private Declare Function FindWindow Lib "user32" _
[*]Alias "FindWindowA" ( _
[*]ByVal lpClassName As String, _
[*]ByVal lpWindowName As String) As Long
[*]Private Declare Function GetWindowLong Lib "user32.dll" _
[*]Alias "GetWindowLongA" ( _
[*]ByVal hwnd As Long, _
[*]ByVal nIndex As Long) As Long
[*]Private 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
[*]Private Declare Function CallNextHookEx Lib "user32" ( _
[*]  ByVal hHook As Long, _
[*]ByVal nCode As Long, _
[*]ByVal wParam As Long, _
[*]lParam As Any) As Long
[*]Private Declare Function UnhookWindowsHookEx Lib "user32" ( _
[*]ByVal hHook As Long) As Long
[*]Private Declare Function PostMessage Lib "user32.dll" _
[*]Alias "PostMessageA" ( _
[*]ByVal hwnd As Long, _
[*]ByVal wMsg As Long, _
[*]ByVal wParam As Long, _
[*]ByVal lParam As Long) As Long
[*]Private Declare Function WindowFromPoint Lib "user32" ( _
[*]ByVal xPoint As Long, _
[*]ByVal yPoint As Long) As Long
[*]Private Declare Function GetCursorPos Lib "user32.dll" ( _
[*]ByRef lpPoint As POINTAPI) As Long
[*]Private Const WH_MOUSE_LL As Long = 14
[*]Private Const WM_MOUSEWHEEL As Long = &H20A
[*]Private Const HC_ACTION As Long = 0
[*]Private Const GWL_HINSTANCE As Long = (-6)
[*]Private Const WM_KEYDOWN As Long = &H100
[*]Private Const WM_KEYUP As Long = &H101
[*]Private Const VK_UP As Long = &H26
[*]Private Const VK_DOWN As Long = &H28
[*]Private Const WM_LBUTTONDOWN As Long = &H201
[*]Private Const cSCROLLCHANGE As Long = 10
[*]Private mLngMouseHook As Long
[*]Private mFormHwnd As Long
[*]Private mbHook As Boolean
[*]Dim mForm As Object
[*]Sub HookFormScroll(oForm As Object)
[*]Dim lngAppInst As Long
[*]Dim hwndUnderCursor As Long
[*]Set mForm = oForm
[*]hwndUnderCursor = FindWindow("ThunderDFrame", oForm.Caption)
[*]Debug.Print "Form window: " & hwndUnderCursor
[*]If mFormHwnd <> hwndUnderCursor Then
[*]UnhookFormScroll
[*]Debug.Print "Unhook old proc"
[*]mFormHwnd = hwndUnderCursor
[*]lngAppInst = GetWindowLong(mFormHwnd, GWL_HINSTANCE)
[*]If Not mbHook Then
[*]mLngMouseHook = SetWindowsHookEx( _
[*]WH_MOUSE_LL, AddressOf MouseProc, lngAppInst, 0)
[*]mbHook = mLngMouseHook <> 0
[*]If mbHook Then Debug.Print "Form hooked"
[*]End If
[*]End If
[*]End Sub
[*]Sub UnhookFormScroll()
[*]If mbHook Then
[*]UnhookWindowsHookEx mLngMouseHook
[*]mLngMouseHook = 0
[*]mFormHwnd = 0
[*]mbHook = False
[*]End If
[*]End Sub
[*]Private Function MouseProc( _
[*]ByVal nCode As Long, ByVal wParam As Long, _
[*]ByRef lParam As MOUSEHOOKSTRUCT) As Long
[*]On Error GoTo errH 'Resume Next
[*]If (nCode = HC_ACTION) Then
[*]Debug.Print "action"
[*]Debug.Print "right window"
[*]If wParam = WM_MOUSEWHEEL Then
[*]Debug.Print "mouse scroll"
[*]MouseProc = True
[*]If lParam.hwnd > 0 Then
[*]mForm.ScrollTop = Application.Max(0, mForm.ScrollTop - cSCROLLCHANGE)
[*]Else
[*]mForm.ScrollTop = Application.Min(mForm.ScrollHeight - mForm.InsideHeight, mForm.ScrollTop + cSCROLLCHANGE)
[*]End If
[*]Exit Function
[*]End If
[*]End If
[*]MouseProc = CallNextHookEx( _
[*]mLngMouseHook, nCode, wParam, ByVal lParam)
[*]Exit Function
[*]errH:
[*]UnhookFormScroll
[*]End Function
[/LIST]
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,112
Office Version
  1. 2016
Platform
  1. Windows
You will need to calculate the value (Top+Height) of the bottom-most control on the userform and set the ScrollHeight Property of the userform to that value.
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
165
You will need to calculate the value (Top+Height) of the bottom-most control on the userform and set the ScrollHeight Property of the userform to that value.
Hi Jaafar,
In the properties tab of the userform i have the scroll top=0 and scrollheight=5500... is there something else i should be doing?

many thanks,

mike
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,112
Office Version
  1. 2016
Platform
  1. Windows
Hi Jaafar,
In the properties tab of the userform i have the scroll top=0 and scrollheight=5500... is there something else i should be doing?

many thanks,

m

As I said, locate the bottom most control you have on the userform, add the control Top value to the control Height Value and then set the userform ScrollHeight Property to that value
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
165

ADVERTISEMENT

As I said, locate the bottom most control you have on the userform, add the control Top value to the control Height Value and then set the userform ScrollHeight Property to that value
Hi Jaafar,

I appreciate your help, i did as you suggested. Initially my scrollheight was set to 5000, using your method it is now set at 4950 but it changes nothing.
When i scroll its almost like it has taken a screen grab and the area visible on the userform srolls but the unseen area below doesnt appear, it just scrolls down into grey.
If i click and drag the scroll bar then i can see it all fine, its only when using the mouse wheel that i have the issue.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,112
Office Version
  1. 2016
Platform
  1. Windows
Hi Jaafar,

I appreciate your help, i did as you suggested. Initially my scrollheight was set to 5000, using your method it is now set at 4950 but it changes nothing.
When i scroll its almost like it has taken a screen grab and the area visible on the userform srolls but the unseen area below doesnt appear, it just scrolls down into grey.
If i click and drag the scroll bar then i can see it all fine, its only when using the mouse wheel that i have the issue.
Are you able to upload a sample of your workbook to some file sharing website and post a link here so we can take a look ?
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
165
Are you able to upload a sample of your workbook to some file sharing website and post a link here so we can take a look ?
Jaafar i managed to sort it, i made a proper stupid mistake!
I hadnt put the "hookformscroll me" command in the multipage change, just the userform initialize so it was just scrolling through the userform without taking into account the multipage.

Phew, thats a relief!
Thank you so much for your support and help,

Mike
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

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
Top