Code for mouse wheel in VBA

[Colleen45]

What VBA code do I use to use the mouse wheel on my own form, or is on one of the properties
Thank you

 

[guidoa961]

Hi Rory,
I would like to use your code but I'm not sure what to do in order to suit my need;
track the mouse wheel movements up and down in order to update a row counter I have in a cell of a sheet.

Instead of a userform I have an Excel 2016 Sheet.
I don't want to take complete control of the mouse, just get a parameter (boolean or whatever) which tells me if the wheel has been moved up or down and then update the cell row counter (3 rows per movement are the default I think) every time the wheel is moved and leave all the rest to Excel. Is it possible?

Thank you for your time Rory (or whoever can help).

Guido

 

[Cristian Buse]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

I believe it is much easier to have the scrolling code in a separate re-usable module. Otherwise you need to repeat the same code for all Forms that you want to hook.
Take a look at my repository https://github.com/cristianbuse/VBA-UserForm-MouseScroll. You only have to call one line of code from your Userfom and it also works with ComboBoxes, Textboxes, Frames, Multipages etc. Horizontally also. Enjoy!

 

[guidoa961]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

Thanks Cristian!
Yours sounds like an interesting solution but unfortunately I haven't moved to 64bit yet. Sooner or later I'll change my 10y/o pc; that will be a 64 bit one. For now I have a good 32bit solution.
Have a nice day!
Guido

 

[Cristian Buse]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

Thanks Cristian!
Yours sounds like an interesting solution but unfortunately I haven't moved to 64bit yet. Sooner or later I'll change my 10y/o pc; that will be a 64 bit one. For now I have a good 32bit solution.
Have a nice day!
Guido

Hi Guido, my solution works on both 32 and 64 bits

 

[Jaafar Tribak]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

@Cristian Buse

I have just taken a quick look at your code (project) and I think is great ! I liked the idea of passing the scroll control to the parent container when reaching the scroll limit.

I haven't studied your code thoroughly but a quick test of the Scroll and Move events located in the Class module raised the following issue :

If you place a Msgbox inside the events, the scrolling stops working after first run... Try this for example:

Private Sub m_UserForm_Scroll(ByVal ActionX As MSForms.fmScrollAction, ByVal ActionY As MSForms.fmScrollAction, ByVal RequestDx As Single, ByVal RequestDy As Single, ByVal ActualDx As MSForms.ReturnSingle, ByVal ActualDy As MSForms.ReturnSingle)
    If ActionY = fmScrollActionFocusRequest Or ActionY = fmScrollActionControlRequest Then ActualDy = 0
    If ActionX = fmScrollActionFocusRequest Or ActionX = fmScrollActionControlRequest Then ActualDx = 0
[COLOR=#FF0000][B]    MsgBox "hello"[/B][/COLOR]
End Sub

Also, another thing that you may want to look at is the fact that scrolling a listbox control horizontally doesn't work .

Thanks very much for sharing

 

[Cristian Buse]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

@Jaafar Tribak

Good point with the MessageBox. It unkooks because the Userform is not active anymore. The messagebox is. This is a mechanism I've put in place to prevent crashes.
The easy solution would be bo hook again after the message box but you actually gave me an idea which I will implement in the following days.
by the way if you need a Scroll event you can place your own in the Userform. The one in the class has a very specific purpose and should be left as is.

Yes, scrolling a Listbox does not work. Yet. If you look in the ScrollX method, I've only implemented Frame, Form, MultiTab. This is on the TODO list.

Thanks for your feedback!

 

[Cristian Buse]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

@Jaafar Tribak

Again, thanks Jaafar! I went ahead and applied a fix so MessageBoxes now work. It was easier than I initially thought.

I believe I've missed a detail in my previous comment. The Vertical scroll works for most scrollable controls including Listboxes.
What I missed was to say that the Horizontal scroll gets a bit more complicated especially for TextBoxes and like you mentioned I actually ignored the Listbox entirely when it comes to horizontal scroll. As mentioned before, it's on my TODO list.

 

[Jaafar Tribak]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

Thanks cristian.

Just one important thing : Your code uses a Windows hook so while the hook is installed, any unhandled error will crash the whole excel application !!

Also, I have noticed that the code doesn't work with Modeless userforms.

I like your code design very much but I thought I should mention these issues specially the crashing issue problem.

Regards.

 

[Jaafar Tribak]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

You may want to take a look at the following code where I used a loop and the MSAA API to achieve a generic Mouse Scroll event in userforms.

With this loop approach, unhandled errors won't crash the excel application

https://www.mrexcel.com/forum/excel...-userform-all-its-controls-4.html#post5154915

Regards.

 

[YasserKhalil]

Re: Code for mouse wheel to scroll Frame on a Userform in VBA

@Jaafar Tribak
I need your help at this topic as you are expert at APIs