Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    25

    Unhappy Unanswered: Sync Scrolling of Two Listboxes

    I'm trying to have two listboxes scroll each other with the following code:

    Private Sub List1_Scroll()
    List2.TopIndex = List1.TopIndex
    End Sub

    Private Sub List2_Scroll()
    List1.TopIndex = List2.TopIndex
    End Sub

    This is great for VB, but VBA doesn't offer a Scroll Event for a listbox. The scrollbars that automatically appear on Access listboxes don't seem to fire any events (BeforeUpdate, AfterUpdate, etc.).

    I'm trying to NOT use a bulky ActiveX control to sync two listboxes (if possible) and am not sure if creating some kind of class using the WithEvents keyword is the way to go. Is there a way to access a native Access control event for this purpose?

    FYI - The top listbox will just show the Column Heads and the botton listbox will show the records WITHOUT the Column Heads. I'm doing this because the bottom listbox's RowSource is set to an SQL string and if the ColumnHeads property is not set to False when setting the
    RowSource, the field headings become the first row in the listbox causing .Column(0, 0) to become "Column_Name" instead of the actual Column value. This throws off the index by 1, creating a subtle error

    I appreciate any and all help - Thanks !!!

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Sync Scrolling of Two Listboxes

    Originally posted by sat_8051
    FYI - The top listbox will just show the Column Heads and the botton listbox will show the records WITHOUT the Column Heads. I'm doing this because the bottom listbox's RowSource is set to an SQL string and if the ColumnHeads property is not set to False when setting the
    RowSource, the field headings become the first row in the listbox causing .Column(0, 0) to become "Column_Name" instead of the actual Column value. This throws off the index by 1, creating a subtle error
    I'd really like to help... but I'm sorry, I'm confused... Can I ask a few questions while I think about this?

    1) I don't understand how the actual column values starting at (1,0) creates an error... How would this be a problem in designing an application?

    2) If ListBox1 is only going to contain Column Headings... ie only one row... why would you need to sync the scrolling? ...There's one row in ListBox 1 and Many rows in ListBox 2... isn't there?

    3) Wouldn't it be easier to use labels to display the column headings above the ListBox with the actual records from the table?

    Sorry if I'm completely misunderstanding your question... Thanks!

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    To accomplish what you want, we need to access two functions from the Comctl32.DLL and one function from the User32.DLL. The FlatSB_GetScrollPos and FlatSB_SetScrollPos are pretty much self explanitory. The GetFocus function is required in order to get the handle (hWnd) of your List Box controls (or any control for that matter). MSAccess does not give this feature as it does for Forms (Me.hWnd) so we need to use API calls to accomplish this.

    Copy and Paste this code into a database module.

    Code:
    Declare Function FlatSB_SetScrollPos Lib "comctl32" (ByVal hWnd As Long, ByVal code As Long, _
    ByVal nPos As Long, ByVal fRedraw As Boolean) As Long
    Declare Function FlatSB_GetScrollPos Lib "comctl32" (ByVal hWnd As Long, _
    ByVal code As Long) As Long
    '-----------------------------------------
    Public Declare Function apiGetFocus Lib "user32" Alias "GetFocus" () As Long
    'Used to get the Handle of a Control.
    '------------------------------------------
    
    Public Const SB_HORZ = 0
    Public Const SB_VERT = 1
    Public Const SB_BOTH = 3
    
    
    Public Function fhWnd(ctl As Control) As Long
        On Error Resume Next
        ctl.SetFocus
        If Err Then
            fhWnd = 0
        Else
            fhWnd = apiGetFocus
        End If
        On Error GoTo 0
    End Function
    In the Declarations section of your Form which contains the List Boxes, place the following dimension:

    Code:
    Dim hWnd_A as Long
    Dim hWnd_B as Long
    In the GotFocus event of List1, copy and paste this code:
    Code:
    hWnd_A = fhWnd(Me.List1)
    hWnd_B = fhWnd(Me.List2)
    Me.TimerInterval = 1
    In the GotFocus event of List2, copy and paste this code:

    Code:
    hWnd_A = fhWnd(Me.List2)
    hWnd_B = fhWnd(Me.List1)
    Me.TimerInterval = 1
    In the LostFocus event of both List1 and List2, copy and paste this code:

    Code:
    Me.TimerInterval = 0
    Now....In the OnTimer event for the Form which contains your List Boxes, copy and paste this code:

    Code:
    FlatSB_SetScrollPos  hWnd_B, SB_VERT, FlatSB_GetScrollPos(hWnd_A, SB_VERT), False
    This should work...

    To get property info on and to manipulate scroll bars, check out these API functions:


    FlatSB_EnableScrollBar
    FlatSB_GetScrollPos
    FlatSB_GetScrollInfo
    FlatSB_GetScrollProp
    FlatSB_GetScrollRange
    FlatSB_SetScrollInfo
    FlatSB_SetScrollPos
    FlatSB_SetScrollProp
    FlatSB_SetScrollRange
    FlatSB_ShowScrollBar


    Hope it helps '----------------------------------------- '-----------------------------------------

  4. #4
    Join Date
    Jul 2002
    Posts
    25

    Talking THANKS !!!!!

    Trudi - The reason that I want to use two listboxes that sync their scrolling is because I have a recordset with 41 columns (fields). I can't use Column Heads in the primary listbox because I snag the selection and use it in my code. There are just too many columns to show without scrolling, but I want to give my users a heading so they know what field they are looking at as they scroll through the columns from left to right. Without getting into too much code, but allow you to see why having Column Heads in the first row would cause problems:

    ' Set Control object variable to point to the listbox
    Set ctlSearchResults2 = Me.lstSearchResults2
    ' Find the selected item because MultiSelect is set to None
    For Each varItem In ctlSearchResults2.ItemsSelected
    varInPatientIncidentID = ctlSearchResults2.ItemData(varItem)
    Next varItem
    ' Set lngInPatientIncidentID = InPatientIncidentID (bound column) of the selected item

    ' Code skipped here ...

    ' Making a selection in lstSearchResults2 exposes the InPatientIncidentID, the bound
    ' column of lstSearchResults2. Since the InPatientIncidentID uniquely identifies a
    ' single record in tblInPatientIncidents, the underlying recordset of the form, it is
    ' used to directly search Me.Recordset for the matching record.

    By not including Column Heads I don't get heading names when the user clicks on the first row by mistake.
    -----------------------------------------------------------------------------

    CyberLynx - Incredible !!! How do you get a repertoire of Win32 API functions like that - unbelievable !!! I'll implement your powerful suggestion this morning and will report on its success. I can't tell you how thankful I am for you to take the time to share your wealth of knowledge !!! I believe that Win32API calls will not result in nearly the performance hit that a full-blown ActiveX control would produce. This is remarkable and I can quickly see where this could be used for a number of situations. Thanks again for your help. I hope that it helps out others as well since this could easily be used for a number of problems. You are the man !!!

  5. #5
    Join Date
    Jul 2002
    Posts
    25

    Unhappy I ran into a leetle problem ...

    Well, things didn't go as planned. Everything compiles, but I get a 43 second delay when I hit the tab where these two listboxes exist, there is a delay anytime I click on the scroll bar of either listbox and moving the scroll bar of either listbox does NOT move the scroll bar of the alternate listbox. It may be a problem with how Access creates these pseudo-window controls at runtime.

    The main difference between my code and what you had listed was that I changed SB_VERT to SB_HORZ in the Timer event because the scroll bars of interest are Horizontal Scroll Bars. I changed it back to SB_VERT and tried it out just to test things and I had the same 43 second delay and a delay when I moved from the scroll bar of one listbox to the scroll bar of the other scroll bar. Better testing showed that after you move the scroll bar of one listbar, moving that same listbox doesn't cause a delay.

    Here is what I did:

    First, I created a new Module, named it "basScrollPosition" and added the following code:
    ================================================== ================================
    Option Explicit ' Force explicit variable declaration
    Option Compare Database

    '---------------------------------------------------------------------------
    ' Scroll functions found in the Comctl32.DLL
    '
    ' Syntax: int FlatSB_SetScrollPos(HWND hwnd, int code, int nPos, BOOL fRedraw);
    ' Parameters:
    ' hWnd - Handle to the window that contains the flat scroll bar.
    ' code - Parameter that specifies the scroll bar type. It can be 1 of the following values:
    ' SB_HORZ - Sets the thumb position of the horizontal scroll bar.
    ' SB_VERT - Sets the thumb position of the vertical scroll bar.
    ' nPos - Parameter that specifies the new thumb position.
    ' fRedraw - Parameter that specifies whether the scroll bar should be redrawn immediately
    ' to reflect the change. If this parameter is TRUE, the scroll bar is redrawn; if it is
    ' FALSE, the scroll bar is not redrawn.
    Declare Function FlatSB_SetScrollPos Lib "comctl32" (ByVal hWnd As Long, _
    ByVal code As Long, ByVal nPos As Long, ByVal fRedraw As Boolean) As Long

    ' Syntax: int FlatSB_GetScrollPos(HWND hwnd, int code);
    ' Parameters:
    ' hWnd - Handle to the window that contains the flat scroll bar.
    ' code - Parameter that specifies the scroll bar type. It can be 1 of the following values:
    ' SB_HORZ - Sets the thumb position of the horizontal scroll bar.
    ' SB_VERT - Sets the thumb position of the vertical scroll bar.
    Declare Function FlatSB_GetScrollPos Lib "comctl32" (ByVal hWnd As Long, _
    ByVal code As Long) As Long
    '---------------------------------------------------------------------------


    '---------------------------------------------------------------------------
    ' Scroll function found in the User32.DLL
    '
    ' Function below is used to Get the Handle of a Control
    Public Declare Function apiGetFocus Lib "user32" Alias "GetFocus" () As Long
    '
    ' Access controls are not standard VB controls. They're drawn on the screen at runtime.
    ' As such, unlike VB controls, they do not have a unique hWnd.

    ' When an Access control on a form receives the focus, it becomes a true window and it's
    ' possible to retrieve it's handle by using the GetFocus API. Note that because Access
    ' controls do not behave like VB controls, there's not a whole lot that we can do with
    ' the hWnd.
    '---------------------------------------------------------------------------

    Public Const SB_HORZ = 0
    Public Const SB_VERT = 1
    Public Const SB_BOTH = 3

    Public Function fhWnd(ctl As Control) As Long
    On Error Resume Next
    ctl.SetFocus
    If Err Then
    fhWnd = 0
    Else
    fhWnd = apiGetFocus
    End If
    On Error GoTo 0
    End Function
    ================================================== ================================


    Second, In the frmInPatientIncidentsDataEntry form (The form with the 2 listboxes)
    I added the following code:


    In the Declarations section, I added:
    ================================================== ================================
    Option Explicit ' Force explicit variable declaration.

    Dim hWnd_A As Long
    Dim hWnd_B As Long

    Option Compare Database
    ================================================== ================================


    In the ListBox1's GotFocus Event I added the following code:
    ================================================== ================================
    Private Sub lstSearchResults2Header_GotFocus()

    ' Add this code to the GotFocus Event of Listbox1
    'hWnd_A = fhWnd(Me.Listbox1)
    'hWnd_B = fhWnd(Me.Listbox2)
    'Me.TimerInterval = 1

    ' Retrieve the handle of the Listbox1 Control with the fhWnd function
    ' in the basScrollPosition Module and assign that value to hWnd_A
    hWnd_A = fhWnd(Me.lstSearchResults2Header)
    ' Retrieve the handle of the Listbox2 Control with the fhWnd function
    ' in the basScrollPosition Module and assign that value to hWnd_B
    hWnd_B = fhWnd(Me.lstSearchResults2)
    ' The TimerInterval property specifies the interval, in milliseconds, between Timer events
    ' on a form. Code in the form's Timer event procedure (OnTimer) repeats at this interval
    Me.TimerInterval = 1000

    End Sub
    ================================================== ================================


    In the ListBox1's LostFocus Event I added the following code:
    ================================================== ================================
    Private Sub lstSearchResults2Header_LostFocus()

    ' Put this code into the LostFocus event of both Listbox1 and Listbox2
    ' Setting the TimerInterval property to 0 prevents the Timer event from occurring
    Me.TimerInterval = 0

    End Sub
    ================================================== ================================


    In the ListBox2's GotFocus Event I added the following code:
    ================================================== ================================
    Private Sub lstSearchResults2_GotFocus()

    ' Add this code to the GotFocus Event of Listbox2
    'hWnd_A = fhWnd(Me.Listbox2)
    'hWnd_B = fhWnd(Me.Listbox1)
    'Me.TimerInterval = 1

    ' Retrieve the handle of the Listbox2 Control with the fhWnd function
    ' in the basScrollPosition Module and assign that value to hWnd_A
    hWnd_A = fhWnd(Me.lstSearchResults2)
    ' Retrieve the handle of the Listbox1 Control with the fhWnd function
    ' in the basScrollPosition Module and assign that value to hWnd_B
    hWnd_B = fhWnd(Me.lstSearchResults2Header)
    ' The TimerInterval property specifies the interval, in milliseconds, between Timer events
    ' on a form. Code in the form's Timer event procedure (OnTimer) repeats at this interval
    Me.TimerInterval = 1000

    End Sub
    ================================================== ================================


    In the ListBox2's LostFocus Event I added the following code:
    ================================================== ================================
    Private Sub lstSearchResults2_LostFocus()

    ' Put this code into the LostFocus event of both Listbox1 and Listbox2
    ' Setting the TimerInterval property to 0 prevents the Timer event from occurring
    Me.TimerInterval = 0

    End Sub
    ================================================== ================================


    In the frmInPatientIncidentsDataEntry form's Time Event I added the following code:
    ================================================== ================================
    Private Sub Form_Timer()

    ' Pass the following parameters to the FlatSB_SetScrollPos function in the
    ' basScrollPosition Module:
    ' Parameters:
    ' integer handle stored in hWnd_B
    ' SB_HORZ (For "Scroll Bar-Horizontal" Thumb Position). Alternately, use
    ' SB_VERT for "Scroll Bar-Vertical" Thumb Position
    ' FlatSB_GetScrollPos(hWnd_A, SB_HORZ) passed to specify the new thumb position

    ' In other words, Get the Horizontal Scroll Bar's Thumb position for the control specified
    ' by the hWnd_A window handle using the FlatSB_GetScrollPos function, and then set the
    ' Horizontal Scroll Bar's Thumb position of the control specified by hWnd_B to the same
    ' thumb position as that retrieved from hWnd_A

    FlatSB_SetScrollPos hWnd_B, SB_HORZ, FlatSB_GetScrollPos(hWnd_A, SB_HORZ), False

    End Sub
    ================================================== ================================

    I'm using Access 2000 as a front-end application to tables stored in an Access97 database. Since all of my code here is in Access 2000 and doesn't hit any tables at this point, I don't think that should have any impact, but I thought that I should mention it. I'll take a look at the other API's that you mentioned and see if they can be used. If you see something that you think is wrong in my code, please let me know and I'll gladly make the change and give it a try.

    Thanks again for all of the excellent code and outstanding help !!! Although it didn't work for me, I'm still very grateful for everyone's time and energy on this !!!

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Reduce the TimerInterval from 1000 to 1 (1000 = 1 Second). The key note to remember here is not to loose the handle (hWnd) of your control (even a tool tip can do that). Because Access doesn’t support individual handles for controls (and I'm sure there is a good reason for this) getting, holding and maintaining the handle can be tricky at best. It may be equally important that each list box contain the same number of items for the code provided to function properly. Anything different would most likely require tweaking (so to speak).

    I quickly pumped out this code so as to point you into some direction in which to assist you with accomplishing your desired task. I did not fully test it since doing so would be meaningless. No two applications are developed the same and this most often applies to the operating environment as well. With this in mind, it will be most certain that code tweaking will be required from one point to another.

    Start small....create a new application with only the task a hand within your project (two list boxes). Rather than getting the effect you want right away, perhaps have the code located within the OnTimer event display the results from the FlatSB_SetScrollPos function display into a text box control and the results from the FlatSB_GetScrollPos function display in yet another text box. Ensure that you are getting the results you expect when the project is compiled and run. If you are satisfied with the test then apply it to the actual list boxes and test again. When you are finally happy with the code and all is working the way you want, apply it to your application.

    Unless I have your project in front of me, I am truly of little help with your quest....I can only point you into the direction to travel.

  7. #7
    Join Date
    Jul 2002
    Posts
    25

    Wink On my way

    CyberLynx,

    I appreciate your wise exhortation. I think some testing is in order. I'm even thinking about creating a VB ActiveX DLL that will subclass an Access form and capture the Windows messages for scrolling. A reference to the DLL could be made in Access and code could be embedded within events of the two listboxes to utilize this information similar to the logic that you have shown. This would create a very small ActiveX DLL file that would not significantly impact performance.

    Again, I appreciate you directing my footsteps to put me on the right path. Now the real journey begins ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •