Bind 2nd scrollbar to the scrollbar of a List Box

[Cavok]

I have a main form with a subform. The subform holds a very wide list box control making the subform and the List Box control wider than the main form itself. The list box control has a vertical scroll bar but as the list box on the subform is wider than the main form the scroll bar is only visible by scrolling the subform to the right first.

Basically, what I need is, a scroll bar on the main form, that controls the scroll bar of the list box on the subform. That way, I can always scroll the list box up or down, regardless of the listbox scrollbar itself being visible or not.
It looks like there's no easy way of doing this though. I found a separate ActiveX scroll bar control but I can't find a way how I can use this one to control the scroll bar of a list box.

Any help in this would be much appreciated. Thanks!

[LaVolpe]

Welcome to the forums.

Why not add a horizontal scrollbar to your listbox? You wouldn't need 2 forms; but even if 2 forms is desirable, this could be much easier. And the listbox only needs to be as wide as needed. The code would look something like this....

 ' in your declarations section (top of the form)
Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As Any) As Long
Private Const LB_SETHORIZONTALEXTENT As Long = &H194

Private Sub Command1_Click()
    ' change NrPixels to a valid value. Like maybe the listbox's width * 4 or something.
    ' The value must be in pixels though; use ScaleX() if needed or divide twips value by Screen.TwipsPerPixelX
    SendMessage List1.hwnd, LB_SETHORIZONTALEXTENT, NrPixels, ByVal 0&
End Sub

[Cavok]

Hi, thanks for your reaction. Yes a horizontal scrollbar sounds very logic indeed, but in my case I have textboxes added underneath each column in the listbox. I use these for filtering the column data in the listbox. I want these textboxes to be aligned with the corresponding columns in the listbox, so as soon as I start scrolling the listbox horizontally the alignment is gone. That's the reason why I'm currently using the horizontal scrollbar on the subform, but as said this hides the vertical scrollbar of the listbox now and then. It is still workable by using the scrollwheel on the mouse but it would be more nice of course if the vertical scrollbar is just visible all of the time...

[LaVolpe]

Well, what you are asking is possible, but a re-think on your design may also be desirable.

Here's how to sync them up.... Test to make sure all works ok. Replace List1 with your subform's listbox name.
1. Add a Vertical scrollbar to your main form
2. In the main form add these declarations

Private Declare Function GetScrollInfo Lib "user32.dll" (ByVal hWnd As Long, ByVal n As Long, ByRef lpScrollInfo As SCROLLINFO) As Long
Private Type SCROLLINFO
    cbSize As Long
    fMask As Long
    nMin As Long
    nMax As Long
    nPage As Long
    nPos As Long
    nTrackPos As Long
End Type
Private Const SB_VERT As Long = &H1
Private Const SIF_RANGE As Long = &H1
Private Const SIF_PAGE As Long = &H2

3. Add this to your form. Call this routine each time you first show your subform and after the listbox has been populated

Private Sub SyncScrollbars(theListBox As VB.ListBox)
    Dim SCI As SCROLLINFO
    SCI.cbSize = Len(SCI)
    SCI.fMask = SIF_RANGE Or SIF_PAGE
    GetScrollInfo theListBox.hWnd, SB_VERT, SCI
    VScroll1.Max = SCI.nMax - SCI.nPage + 1&
    If VScroll1.Max = 0 Then
         VScroll1.Enabled = False
    Else
         VScroll1.Enabled = True
         VScroll1.Value = theListBox.TopIndex
    End If
End Sub
' sample call:  SyncScrollbars(subformName.List1)

4. Add these to your main form also

Private Sub VScroll1_Change()
    SubformName.List1.TopIndex = VScroll1.Value
End Sub

Private Sub VScroll1_Scroll()
    SubformName.List1.TopIndex = VScroll1.Value
End Sub

5. Now in your subform, you want to sync up the main form's scrollbar when the subform's listbox moves too. Add this code in that Listbox's Scroll event. Change List1 to the listbox name

Private Sub List1_Scroll()
    MainFormName.VScroll1.Value = List1.TopIndex
End Sub

Play.

Edited: The above code does not take into consideration, adding/removing list items after scrollbars have been sync'd.

Problem noted: If you click on the listbox and use mousewheel to scroll the listbox, the listbox does not get scroll events; but I can't reproduce it 100%; worth looking into. The sync only appears to work perfectly if either scrollbar is scrolled by clicking/dragging with the mouse or via the keyboard (page up/down, etc).
This will get you started, but you'll want a workaround for the mousewheel failing to send VB the Scroll events of the listbox.
See if you can replicate the problem I described: After sync'ing, the Listbox scrolls but main form's scrollbar does not

Edited yet again. Ok, here's how to reproduce the problem. Though I can reproduce it with other scenarios too.
Click on the listbox scrollbar. Then click on the subform. Now use the wheelmouse. The listbox will scroll, but no scroll events are sent to it. The dual sync is contingent on both controls knowing they are each being scrolled. If VB doesn't tell us that then the controls are unaware and more drastic steps, like subclassing, may be needed for a perfect solution.

[Cavok]

Thanks for the comprehensive answer, that looks great! I'm going to play with that tomorrow and will let you know about the results. Thanks again.

[Cavok]

Ok, so now I tried using the code in my application, but as I'm using VBA (Access) I can't get the scrollbar's windowhandle using the .hWnd property. I've looked into the FindWindowEx function but this also doesn't seem to work. Any idea how I can get the window handle of the scrollbar in VBA? Thx.

[LaVolpe]

... but as I'm using VBA (Access) I can't get the scrollbar's windowhandle using the .hWnd property. I've looked into the FindWindowEx function but this also doesn't seem to work. Any idea how I can get the window handle of the scrollbar in VBA? Thx.

Hmmm, problem I think. Offhand, I don't know but you may want to search the MS Office part of the forums. Also, the listbox in VBA may not even have a scroll event which means you can't update the main form's scrollbar when the listbox scrolls; not without subclassing. I'm no longer that familiar with VBA controls.

[Hack]

Ok,but as I'm using VBA (Access)

Moved To Office Development