Slaved Listbox Pair

[hatman]

I have two lists that represent pairs of electrical connectors. List_1 is Male Connectors, and List_2 is the mating Female Connectors. The lists are built so that when side-by-side, a mating pair line up next to each other.

I'm trying to build a User Interface that presents the connectors as pairs, but also aloow the user to select EITHER member of the pair.

My first thougt was to use a Listview or a Listbox with 2 columns. Either way, the user selects a full row, rather a single entry in a row.

I finally ended up building a userform with 2 Listboxes adjacent to each other, and a Vertical Scrollbar Control to the right of the pair. I added the following code to the form, to scroll both Listboxes simultaneously:

Private Const Display_Lines As Long = 7

Private Sub ListBox1_Click()

    Me.ListBox2.ListIndex = -1

End Sub

Private Sub ListBox2_Click()

    Me.ListBox1.ListIndex = -1

End Sub

Private Sub ScrollBar1_Change()

    Me.ListBox1.Clear
    Me.ListBox2.Clear
    
    For cnt = Me.ScrollBar1.Value To Me.ScrollBar1.Value + Display_Lines - 1
    
        Me.ListBox1.AddItem Range("List_1").Item(cnt).Value
        Me.ListBox2.AddItem Range("List_2").Item(cnt).Value
        
    Next cnt
    
End Sub

Private Sub UserForm_Initialize()
    
    Me.ScrollBar1.Min = 1
    Me.ScrollBar1.Max = Range("List_1").Count - Display_Lines + 1
    
    Me.ScrollBar1.LargeChange = Me.ScrollBar1.Max / 5
    
    Me.ScrollBar1.Value = 1

End Sub

As far as that goes, it works fairly well. The user scrolls up and down using the Scrollbar control, and paired entries remain adjacent to each other. The user can select an entry from either Listbox, and I can easily harvest that choice through code.

I was hoping, however, that I could support people who use the keyboard for navigation. Focus on one of the Listboxes, then navigate using the up and down arrow keys. When the user gets to the end of the list, the value of the Scrollbar control increments or decrements as required, thus re-populating the lists. I just can't get this to work in a way that is not cludgey. I have worked through about a half-dozen possible methods, and each one has some hitch that makes it either hokey or unworkable.

Has anyone done this successfully, or have any suggestions to make it work?

 

[Kenneth Hobson]

Use a ComboBox.

 

[hatman]

What Property do I need to change in order to select a single entry in column 2? In the default configuration, selecting an item from Column 2 causes the corresponding entry in Column 1 to be set as the Value property.

 

[hatman]

Sorry Kenneth. I can't find a difference in the functionality of the Combobox that allows an entry from the second column to be selected independently.

Anyway, I think I have a solution. It works relatively smoothly, though using the Ontime function seems a little bit cludgey. If anyone has any better suggestions, I'd be glad to hear them.

Private Const Display_Lines As Long = 7

Private L_1 As String
Private L_2 As String

Private Stp As Boolean

Private Sub ListBox1_Change()

    If Stp Then
    
        If Me.ListBox1.ListIndex = 0 Then
        
            If Me.ScrollBar1.Value > 1 Then
            
                Application.OnTime Now() + 1 / 3600 / 1000, "Decrement"
                
            End If
            
        ElseIf Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 Then
        
            If Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
            
                Application.OnTime Now() + 1 / 3600 / 1000, "Increment"
                
            End If
            
        End If
        
    End If

End Sub

Private Sub ListBox2_Change()

    If Stp Then
    
        If Me.ListBox2.ListIndex = 0 Then
        
            If Me.ScrollBar1.Value > 1 Then
            
                Application.OnTime Now() + 1 / 3600 / 1000, "Decrement"
                
            End If
            
        ElseIf Me.ListBox2.ListIndex = Me.ListBox1.ListCount - 1 Then
        
            If Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
            
                Application.OnTime Now() + 1 / 3600 / 1000, "Increment"
                
            End If
            
        End If
        
    End If

End Sub

Private Sub ListBox1_Click()

    Me.ListBox2.ListIndex = -1
    L_2 = ""

End Sub

Private Sub ListBox2_Click()

    Me.ListBox1.ListIndex = -1
    L_1 = ""

End Sub

Private Sub ScrollBar1_Change()

    If Not Me.ListBox1.Value = "" Then
    
        L_1 = Me.ListBox1.Value
        
    End If
    
    If Not Me.ListBox2.Value = "" Then
    
        L_2 = Me.ListBox2.Value
        
    End If
    
    Stp = False

    Me.ListBox1.Clear
    Me.ListBox2.Clear
    
    For cnt = Me.ScrollBar1.Value To Me.ScrollBar1.Value + Display_Lines - 1
    
        Me.ListBox1.AddItem Range("List_1").Item(cnt).Value
        Me.ListBox2.AddItem Range("List_2").Item(cnt).Value
        
    Next cnt
    
    On Error Resume Next
    
    Me.ListBox1.Value = L_1
    Me.ListBox2.Value = L_2
    
    On Error GoTo 0
    
    Stp = True
    
End Sub

Private Sub UserForm_Initialize()

    Stp = True

    L_1 = ""
    L_2 = ""
    
    Me.ScrollBar1.Min = 1
    Me.ScrollBar1.Max = Range("List_1").Count - Display_Lines + 1
    
    Me.ScrollBar1.LargeChange = Me.ScrollBar1.Max / 5
    
    Me.ScrollBar1.Value = 1

End Sub


Private Sub Increment()

    Me.ScrollBar1.Value = Me.ScrollBar1.Value + 1

End Sub


Private Sub Decrement()

    Me.ScrollBar1.Value = Me.ScrollBar1.Value - 1

End Sub

 

[erik.van.geit]

Hi, hatman,

a lot of words a lot of code
and still it is not totally clear to me what you are trying to do
that is I "thought" to know when reading your post, but when trying out your code

seems like you should be able to explain this in a few lines, not ?
so try again and I'll do the same :wink:

kind regards,
Erik

 

[Tim Francis-Wright]

What Property do I need to change in order to select a single entry in column 2? In the default configuration, selecting an item from Column 2 causes the corresponding entry in Column 1 to be set as the Value property.

I think that you want the BoundColumn Property here--it defaults to Column 1 but you can change that as you need.

 

[hatman]

erik.van.geit

a lot of words a lot of code
and still it is not totally clear to me what you are trying to do
that is I "thought" to know when reading your post, but when trying out your code

seems like you should be able to explain this in a few lines, not ?
so try again and I'll do the same :wink:

kind regards,
Erik

Thanx for taking an interest Erik. I'll true to be more succinct...

Here is a sample of my data:

Elect_Tool_X-Ref_Data.xls
	B	C	D	E
1	Connector	Mate
2	BananaPlug	BananaJack
3	ScrewLug	Ring/ForkTerminal
4	PS50GGN	RP50GGN
5	Crocodile/AlligatorClip	GroundPoint2
6	3-Prong	FacilityGroundedOutlet
7	TipPlug	TipJack
8	1553Plug	1553Receptacle
9	DB9MPlug	DB9FReceptacle
10	DB9FPlug	DB9MReceptacle
11	DB25MPlug	DB52FReceptacle
12	DB25FPlug	DB25MReceptacle
13	BNCPlug	BNCReceptacle
Special

Imagine offering this list to the user in a Two-Column ListBox Control. Rather than selecting a full row of data, the user should be able to select a single entry from either the first or second column of the ListBox. Not only should the Value/Text propertes be set to the entry selected, but also the Cursor should highlight only that single entry, and not the entire row.

Tim Francis-Wright

I think that you want the BoundColumn Property here--it defaults to Column 1 but you can change that as you need.

Tim, admittedly I hadn't ever used the BoundColumn or TextColumn properties (which are available in both the ComboBox and ListBox). Until you suggested them, I hadn't really looked into exactly what they did. I thank you for bringing them to my attention. I assume that you were thinking about code like this:

Private Sub ComboBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    'Combobox1.width = 150
    'Combobox1.ColumnWidths = 75;75

    If X< 75 Then
    
        Me.ComboBox1.BoundColumn = 1
        Me.ComboBox1.TextColumn = 1
        
    Else
    
        Me.ComboBox1.BoundColumn = 2
        Me.ComboBox1.TextColumn = 2
        
    End If

End Sub

Or for a ListBox, this:

Private Sub ListBox1_Mouseup(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    'Listbox1.width = 150
    'Listbox1.ColumnWidths = 75;75

    If X< 75 Then
    
        Me.ListBox1.BoundColumn = 1
        
    Else
    
        Me.ListBox1.BoundColumn = 2
        
    End If
    
    Me.Label1.Caption = Me.ListBox1.Value

End Sub

Both are VERY close to what I am looking for. Except that the entire row is highlighted, rather than the single entry selected. However, these are both close enough that I am going to present them to some of my users and see if they think it's an interface they can live with.

I am still interested in an interface where only the single entry is highlighted rather than the entire row, if it's possible. I feel that this feature makes the interface more intuitive for the user, and requires less "training".

EDIT: I just spoke with a couple of my users, and they are adamant that the interface must also support Keyboard Navigation, which the multicolumn ListBox/Combobox method doesn't seem to do.

 

[erik.van.geit]

currently taking a look
(I've got 20 minutes left)
what would you include in

Keyboard Navigation

 

[hatman]

Use the arrow and/or tab keys to navigate to a single entry.

 

[erik.van.geit]

My time is up. I did some experiments...

1. I would avoid to use mousemove + hooks = unstable.
2. Did you ever hear of the "spreadsheet"control on a userform? Perhaps heck it out.

I'll continue trying some other ideas later on, not sure if they will lead somewhere.