Sync Scrolling of Two Listboxes

[Dennis]

Because there was a previous problem with posts to this
original thread (Sheldon, I didn't receive your post - and
my own reposts didn't show up) I am reposting this thread.

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 !!!

==========================================================

Here is a response that I received that didn't work as
expected, but points out a possible way to go ...

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

 

[Kelvin]

Dennis, Here is another option I suggested. I don't know if you saw this
before or not.

How about setting up your list box in an independent form to be used as a
subform. Make the form wide enough to see all the fields. Set the scroll
bar property of this form to none. Then when you insert this into the main
form, make it the szie you want to see on the main form and set the scrol
lbar property of the subform to horizontal. Then when you scroll, both list
bozes will appear to be scrolling together. Link the data for the listbox
to invisible controls on the main form.

Kelvin