trying to scroll listbox with two commandbutton

[patricktoulon1]

hello i'me trying to scroll a listbox with a commandbutton but that dont work

Option Explicit

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
                              (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
                              lParam As Any) As Long

Private Const WM_VSCROLL = &H115
Const SB_LINEDOWN = 1
Const SB_LINEUP = 0

'event mouseUp for then button  because i need  a listbox focused

Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  ListBox1.SetFocus
   SendMessage ListBox1.[_GethWnd], WM_VSCROLL, SB_LINEUP, ByVal 0&

End Sub

Private Sub CommandButton2_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  ListBox1.SetFocus
   SendMessage ListBox1.[_GethWnd], WM_VSCROLL, SB_LINEDOWN, ByVal 0&

End Sub

Private Sub UserForm_Activate()
ListBox1.List = Evaluate("row(1:30)")
End Sub

can you help me please

 

[Dan_W]

Hello all

I had some time on the weekend to explore this a bit more, and I'm left with more questions than answers.

Basically, I wanted to explore this CursorInRange and CursorOutOfRange point I suggested above, wondering what actually triggers because I was getting odd results. This is what I've learnt:

• Don't run this on a different DPI second monitor - things get weird and wild, but that just may be common sense that I haven't had the benefit of because I rarely use a second monitor. It took me a very long time to work out that it was the monitor that was at fault.
• I made the userform below and will upload it tomrrow after work when I get a chance.
• The userform comprises 2 frames, each containing 1 x listbox and 1 x textbox. At the top of the userform are three buttons and 8 labels.
• The userform, the Left Frame, and the two listboxes each have their own InkCollector class attached to them. When the CursorInRange event is triggered, the backcolor is set to green, and when teh CursorOutOfRange is triggered, it is set to red. (The right frame never changes because there is not InkCollector attached to it) I did experiment with putting another windowed control in the frame - a listview, but then remembered that JT would not be able to view that, so went with a MultiPage control. I have no earthly idea what manner of evil afflicted my laptop at that point, but I abandoned that, so now we have the vanilla results below.
• Because I'm interested in how this can be used with API generated controls, I generated twp Static controls via API - they are identified by an ellipse on each.

Noteworthy points

• The InkCollector Mouse Events return a pX and pY parameters which are unsurprisingly in pixels, not points. Interestingly, and I've just discovered this, the MouseWheel uses X and Y coords (as opposed to pX and pY). It is still in pixel units, but it is the cursors actual location vis-a-vis the screen, and not the window.
• Unlike with MSForms controls, the IC mouse events are triggered irrespective of the windowless controls beneath it. You can see above that the px and py coords are still being reported even though it is over labels/controls/etc.
  • That said, it also applies to Windowed controls - while it doesn't seem to trigger on the frames, it DOES trigger when running over the API static controls.. I don't know why, but I suspect that it has something to do with why the mouse events do not appear to work with the API static controls.
• It is unclear how the InkCollector works with the API static controls - the Mouse events do not seem to trigger. Rather, they are picked up by the underlying hwnd (here the USerform)). I think I must be doing something wrong because when I used this technique on my API generated RichEdit control, it does respond to the Mouse events. That said, Cursor related events appear to trigger fine - and so when I click on the dots, they change color. I managed to do this through the CursorButtonDown and CursorButtonUp events, because MouseUp and MouseDown simply would not trigger. I would add to this that I also change the MouseIcon to a custom PNG file.That appeared to work fine EXCEPT for the API static controls. In fact, I couldn't work out how to change their mouse pointer at all - not even to the built-in versions.
• I had hoped that maybe one could use the CursorInRange and CursorOutOfRange events as a 'cheap' way of triggering the check of the window under the cursor - for the ComboBox hwnd in particular. Long story short - no luck.
• (@patricktoulon1 - You're workbooks 1 and 2 work for me in that respect, but your most recent version (dated last Thursday?) doesn't for some reason.)

I think I understand now what you mean about losing the connection with teh Textbox - I will upload the approach I had tacken previously, but where it currently sits is that the InkCollector has a hwnd and a TargetControl, and that TargetControl is managed in the MouseWheel event The MouseWheel code - which will change - currently looks something like the snippet below. You will see that I SetFocus on the Textbox at the outset:

This.TargetControl.SetFocus

Private Sub IC_MouseWheel(ByVal Button As MSINKAUTLib.InkMouseButton, ByVal Shift As MSINKAUTLib.InkShiftKeyModifierFlags, ByVal Delta As Long, ByVal X As Long, ByVal Y As Long, Cancel As Boolean)
   
      ' The action triggered by the MouseWheel event can be customised, and the default actions bypassed, by setting the
      ' Cancel flag to true in the corresponding Ueerform event.
   
      On Error GoTo ErrHandler
      RaiseEvent MouseWheel(This.TargetControl.Name, Button, Shift, Delta, X, Y, Cancel)
      If Cancel Then Exit Sub
   
      If Not This.TargetControl Is Nothing Then
        Dim CurrentValue As Long, NewValue As Long, ControlMin As Single, ControlMax As Single
        ControlMin = 0
     
        Select Case TypeName(This.TargetControl)
          Case "Frame", "MultiPage", "Page", This.UserFormName
            This.PropertyName = IIf(Shift = 4, "ScrollLeft", "ScrollTop")
            This.PropertyMax = IIf(Shift = 4, "ScrollWidth", "ScrollHeight")
            ControlMax = CallByName(This.TargetControl, This.PropertyMax, VbGet)
         
          Case "ListBox"
            This.PropertyName = "TopIndex"
            ControlMin = 0
            ControlMax = CallByName(This.TargetControl, "ListCount", VbGet)
         
          Case "ProgressBar2", "Slider2", "ScrollBar", "SpinButton"
            This.PropertyName = "Value"
            ControlMin = CallByName(This.TargetControl, "Min", VbGet)
            ControlMax = CallByName(This.TargetControl, "Max", VbGet)
       
          Case "TextBox"
            This.TargetControl.SetFocus
            This.PropertyName = "CurLine"
            ControlMin = 0
            ControlMax = CallByName(This.TargetControl, "LineCount", VbGet)
                 
          Case Else
            ' TODO - TreeView4? ListView4? ComboBox?
        End Select
     
        ' Store the current value of the control's property
        CurrentValue = CallByName(This.TargetControl, This.PropertyName, VbGet)
     
        ' The new value for this property is calulated by adding the the
        ' positive or negative value (depending on the delta) of the StepSize
        ' variable to the above CurrentValue.
        NewValue = IIf(Delta > 0, -This.StepSize, This.StepSize) + CurrentValue
     
        ' Checks to make sure the value does not go out-of-bounds
        If NewValue < ControlMin Then NewValue = ControlMin
        If NewValue > ControlMax Then NewValue = ControlMax
     
        ' Change the value of the given property
        Call CallByName(This.TargetControl, This.PropertyName, VbLet, NewValue)
      End If
ErrHandler:
  End Sub

There's about a dozen more things to say, but I have to head off.

 

[patricktoulon1]

re
hello @Dan_W
X and Y give the points in pixels, yes, but not only do they give them in pixels, but also based on the screen.
That's why I use them in the event in the example with the class module I provided previously.

• I had hoped that maybe one could use the CursorInRange and CursorOutOfRange events as a 'cheap' way of triggering the check of the window under the cursor - for the ComboBox hwnd in particular. Long story short - no luck

In my opinion, you misunderstood how I load the handle of the hovered control.
Look more closely.
and look event of IC and how i use X and Y for Go Out

• CursorOutOfRange work only whith listbox and frame because they are the only ones that have a fixed handle
  

 

[Jaafar Tribak]

Hi,

After conducting a number of tests, I’ve found the InkCollector control to be too inconsistent for my purposes—particularly when it comes to handling mouse wheel events across different versions and bitness configurations of Excel. While this may not be a universal issue, it has certainly proven unreliable in my own environment, which ultimately led me to abandon further exploration. It's a shame, really, as the control showed considerable promise.

Another frustrating limitation is the inability to sink events from the Ink control using the ConnectToConnectionPoint API. Had this been possible, it would have enabled a much more flexible, late-bound approach—eliminating the need to add a reference to the Ink library manually or programmatically via the VBE. It also would have allowed all MouseMove events, including those from the UserForm itself, to be routed through a single, centralized handler—something I think would have made for a very clean and maintainable design.

A couple of design-related suggestions regarding your implementation so far:

From an OOP standpoint, I believe the MouseWheel event handler for the Ink control would be more appropriately placed in the client UserForm module ... I'd suggest creating a dedicated class wrapper to manage the heavier logic—such as determining the control currently under the mouse cursor—and simply use RaiseEvent to notify the main form when a scroll action occurs.

Regarding X and Y coordinate handling, I suggest converting screen coordinates to client coordinates using the ScreenToClient API. Once that’s done, you can convert the result from pixels to points to get precise positioning.

Note:
Additionally, the Cancel argument passed by reference serves no practical purpose in this context, as—unlike the InkControl—mouse wheel functionality is not enabled by default for MSForms controls.

 

[patricktoulon1]

re
Good evening @Jaafar Tribak,
I don't understand your conclusions because for frames, multipages, listboxs, comboboxs, and textboxs, I tested my class on
W10 2013 32-bit
W11 365 with Excel 2019
W11 Excel 2021 64-bit
and it works perfectly.

perhaps there are others parameters to take into account and not just the 32/64 architecture difference

 

[Jaafar Tribak]

re
Good evening @Jaafar Tribak,
I don't understand your conclusions because for frames, multipages, listboxs, comboboxs, and textboxs, I tested my class on
W10 2013 32-bit
W11 365 with Excel 2019
W11 Excel 2021 64-bit
and it works perfectly.

perhaps there are others parameters to take into account and not just the 32/64 architecture difference

Yes, one of the examples you provided did work for me. However, it only succeeds because the InkCollector is recreated during each mouse move event for every control. This is something I’ve been trying to avoid—not only because it results in an inelegant design, but also because it significantly slows down performance on my machine, which isn’t particularly fast.

 

[patricktoulon1]

re

it only succeeds because the InkCollector is recreated during each mouse move event for every control.

No, that's not entirely correct. I create it when I enter and I destroy it when I leave. I can move the mouse over it 10 times, 100 times, it stays the same as long as I haven't left the rectangle.

 

[Jaafar Tribak]

re

No, that's not entirely correct. I create it when I enter and I destroy it when I leave. I can move the mouse over it 10 times, 100 times, it stays the same as long as I haven't left the rectangle.

Would you mind uploading a working example? The thread has grown quite lengthy, and it's becoming difficult to identify the most recent and functional version of the code.

 

[Dan_W]

Thank you both,

When I first came across the InkCollector, I thought it was an interesting tool, - and frankly, I still do! I take your point Jaafar about the inconsistent behaviour - as I discuss above re: how it deals and interacts with API-generated controls. Thoroughly baffled. But I'm not giving up - I'm convinced it's just my own lack of knowledge that is the source of the problem.

Some other oddities I've noticed

• With the CursorDownButton/CursorUpButton - it won't tell you which button is being pressed, which is not overly surprising given that the 'cursor' it's contemplating is not a mouse.
• The Cursor parameter being passed in the event has a WHOLE lot of information packed into it.
• @Jaafar Tribak - re coordinates:

Regarding X and Y coordinate handling, I suggest converting screen coordinates to client coordinates using the ScreenToClient API. Once that’s done, you can convert the result from pixels to points to get precise positioning.

I don't really think I need to - I've updated it in the screen capture, but bascically the MouseWheel X and Y are pixel coordinates for the screen, but the MouseMove pX and pY coordinates do pretty much what I need. My Mouse was in the top left hand side of the window, but the window was in the bottom right hand side of teh screen - thus the discrepancy between the left coords( window) and the right coords (screen).

• CursorOutOfRange work only whith listbox and frame because they are the only ones that have a fixed handle

CursorOutOfRange works for the UserForm, the ListBox, the Frame, but as you can see in my animated capture above, it also worked for the API generated Static controls.

From an OOP standpoint, I believe the MouseWheel event handler for the Ink control would be more appropriately placed in the client UserForm module ... I'd suggest creating a dedicated class wrapper to manage the heavier logic—such as determining the control currently under the mouse cursor—and simply use RaiseEvent to notify the main form when a scroll action occurs.

I had intended this is what my class would do with:

RaiseEvent MouseWheel(This.TargetControl.Name, Button, Shift, Delta, X, Y, Cancel)

The Cancel parameter is actually meant to be like a Handled parameter - bad naming on my part. But the plan is to have all the basic controls covered for with expected behaviour scrolling etc, but for the UserForm to deicde whether or not it needs that. So this would RaiseEVent the MouseWheel for the UserForm to decide what it wants to do. I have multiple versions of this class floating around, and one of them passes the actual control itself back as a param (or it's name) insofar as there is only going to be a single InkCollector Class in play. Of course, there can be multiple InkCollectors at once, but it feels a little excessive.

I'm concious that this doesn't solve every problem or use case with 100% satisfaction, but it does feel like it solves the majority of use cases for the majority of users in a relatively painless way.

At any rate, thank you both with your help in trying to unravel this mystery! I really appreciate it, and I'll let you know where I get to with my class!

 

[Dan_W]

Yes, one of the examples you provided did work for me. However, it only succeeds because the InkCollector is recreated during each mouse move event for every control. This is something I’ve been trying to avoid—not only because it results in an inelegant design, but also because it significantly slows down performance on my machine, which isn’t particularly fast.

This occurred to me as well. My reading of your method, @patricktoulon1 is

1. InkScrolling - it updates ActualControl to the control that triggered the MouseMove. It calls GetWindowFromCurs() to get the hWnd currently under the cursor, and then to the extent that it's different from the current pointer (HandleCible), it updates HandleCible and calls SetupMouseWheel to attach the InkCollector to this new hWnd.
2. SetupMouseWheel - BUT it does this by destroying the previous InkCollector with Set IC = Nothing and then creates a new InkCollector. It attaches it to the passed hWnd (ie, the one under the cursor)

And off we go again.

But why destroy and create the InkCollector over and over again? Why not just assign the existing InkCollector with the new hWnd?

 

[patricktoulon1]

re both
I'm destroying it because if I move from one textbox to another, for example, the handle will necessarily be the same, and therefore the ink doesn't understand it anymore; it's no longer synchronized (actualcontrol/handle).
There's also the fact that for multipages, when you're in the header, it's the handle of the multipage, and when you're in the page, it's the handle of the page. For comboboxes, this isn't new. You know the problem: the handle changes with each development of the child element.

I repeat the principle

I enter (mouse move event of the control) within the control's perimeter
I kill the IC
I create a new IC
actualcontrol becomes the hovered control
I give it focus if it's not a label
I capture the handle under the cursor
i give the focus handle too(BEFORE ASSIGN TO IC !!!!!!!!!!)
I assign the attribute to the IC
I determine the rectangle

As long as I move inside, IC remains, and while it remains, if I move and the handle under the cursor changes, I kill the IC and recreate it with the new handle, but always the same control
If I'm outside, with the X and Y test, the integer constant of the ic_mousewhell event relative to my rectangle, I kill and exit the event

I don't destroy it in the case where I move from one textbox to another, for example, the handle will necessarily be the same, and therefore the ink doesn't understand it anymore; it's no longer synchronized (actualcontrol/handle).
There's also the fact that for multipages, when you're in the header, it's the handle of the multipage, and when you're in the page, it's the handle of the page. For comboboxes, this isn't new. You know the problem: the handle changes with each development of the child element.

the files