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?

 

[hatman]

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

1. I would avoid to use mousemove + hooks = unstable.

I agree. Which is part of why I consider my current solution to be less than desireable (the use of OnTime to delay code execution until the current routine has completed is the other part of why I don't like my current solution)

2. Did you ever hear of the "spreadsheet"control on a userform? Perhaps heck it out.

You know, I have never used it, though now that you mention it, I can dimly remember fielding a question about it at some point in the past. I will definitely look into it.

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

Thank you Erik. I appreciate your input greatly. Even if your suggestions don't necessarily pan out, I know I will at least learn something new along the way :wink:

 

[hatman]

I looked into the Spreadsheet Control.

WOW

Did you realize that it supports a fully functional multi sheet workbook?
Did you know that the sheet range is A1:ZZZ262144? That's 13,824 columns x 262,144 columns

Anyway, it took me a while to lobotomize it enough to perform the lowly functionality I am looking for. Worked perfectly.

BUT!

When a user opens a workbook that contains the Spreadsheet Control, Excel displays a Msgbox that says MSForms is about to open an Active-X application, it could be a virus, if this is coming from a trusted source, then hit okay to enable the control, otherwise, it will be disabled. Unfortunately, that isn't acceptable.

The upshot was that in playing with it, I realized for the first time that the KeyDown/Up event passes in the KeyCode rather than KeyAscii like the KeyPress event (can't imagine why I hadn't noticed that before). What that means is that I can capture the Arrow Keys, thus making my code a little simpler and more stable. More importantly, it solves a couple of minor annoyances in the behaviour of my slaved listbox pair, making the interface much smoother.

This is my next iteration of the code:

Private Const Display_Lines As Long = 7

Private L_1 As String
Private L_2 As String


Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    
    Select Case KeyCode
    
        Case 40 'down
            If Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 And _
                Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value + 1
                    Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1
            End If
            
        Case 38 'up
            If Me.ListBox1.ListIndex = 0 And _
                Me.ScrollBar1.Value > 1 Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value - 1
                    Me.ListBox1.ListIndex = 0
            End If
                    
        Case 39 'right
            Me.ListBox2.ListIndex = Me.ListBox1.ListIndex
            Me.ListBox1.ListIndex = -1
            L_1 = ""
            Me.ListBox2.SetFocus
        
    End Select

End Sub



Private Sub ListBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    
    Select Case KeyCode
    
        Case 40 'down
            If Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1 And _
                Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value + 1
                    Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1
            End If
            
        Case 38 'up
            If Me.ListBox2.ListIndex = 0 And _
                Me.ScrollBar1.Value > 1 Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value - 1
                    Me.ListBox2.ListIndex = 0
            End If
                    
        Case 37 'left
            Me.ListBox1.ListIndex = Me.ListBox2.ListIndex
            Me.ListBox2.ListIndex = -1
            L_2 = ""
            Me.ListBox1.SetFocus
        
    End Select

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

    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
    
End Sub

Private Sub UserForm_Initialize()

    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

I would still be interested in using the Spreadsheet control, if someone could figure out a way around the Security Alert issue. The Spreadsheet control has more fidelity and offers me more flexibility.

 

[erik.van.geit]

yes, that spreadsheetcontrol: I never really used it but made up an example a while ago
can you email me your thing with the spreadsheetcontrol ?
I do not get that securitymessage with the little example I made.

and while you're at it: can you email the slaved listboxes too if possible
I've made up my own example, but it seems easier to work on the same

 

[erik.van.geit]

well received
I'll write a list of thoughts, remarks,.... not all to the point, but while I was at it

missing references:
Microsoft Office XP Web Components
Microsoft Office Web Components 11.0
no spreadsheetcontrol visible
made one again myself: found that there are two versions in the controlspalet on my machine: 10.0 & 9.0

You learned me something (never needed nor noticed): using the X and Y arguments on mouseevents in listbox.
Do you know about this syntax?

Me.ListBox1.List = Range("List_1:List_2").Value

perhaps overkill, but in case you change your columnwidths

Dim w As Variant
w = ListBox1.ColumnWidths
w = Left(w, InStr(w, " ") - 1)

    If X < w Then

1.
I've spent playing quite some time: I would choose for a button
"single or double selection" and switch userforms (or multipage). (Synchronising the listboxes would not be a problem for you.)

2.
NOT GOOD but just to show my "thinking process"
After playing with the idea of a vertical label at the left: when you click there it means you select both items: a hidden "2 column listbox" would replace the two slaved ones.

3.
revolutionary
instead of a listbox use a number of labels (2 columns, 10 rows)
clicking a label: change color to show selection, adjacent label can be colored or not
undo selection = click again
perhaps using an extra first column (small) two highlight the "entire row"
would involve a classmodule not too much coding

EXAMPLE
click L12 = selected
click L12 twice = deselected
click L12, then L13 = only L13 selected
click L22 = selected
click L12, then L22 = both selected
click L2 = both L12 & L 22 selected (click L2 again to deselect)

L1	L11	L21
L2	L12	L22
L3	L13	L23
L4	L14	L24
etcetera

a scrollbar is needed of course

if this sounds reasonable, I would be glad to try out

best regards,
Erik

 

[hatman]

missing references:
Microsoft Office XP Web Components
Microsoft Office Web Components 11.0

I don't have either of those available in my list of possible references when I open a new workbook. The closest I have in the list is Microsoft Office Web Wizards.

BUT, I did some playing, and upon inserting a Spreadsheet Control into a new userform in a new Workbook, suddenly in my references list I observed the addition of Microsoft Office Web Components 11.0, which was already checked off. I closed Excel, opened the file that I sent to you, and observed that BOTH libraries were present and Checked.

I did some poking, and found that:
Microsoft Office Web Components 11.0 = C:\Program Files\Common Files\Microsoft Shared\Web Components\11\OWC11.DLL

and that:
Microsoft Office XP Web Components = C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL

I played a bunch, and couldn't come up with a scenario where the references failed to be present in a saved file... not sure what would have caused this behaviour.

Until you pointed it out, I would not have even noticed that one or both of these references were required for this control.

no spreadsheetcontrol visible
made one again myself: found that there are two versions in the controlspalet on my machine: 10.0 & 9.0

Interesting. Possibly related to the absence of one or more of the above references? Also worthy of note: I have versions 10.0 and 11.0 available for this control. I used version 11.0. Perhaps the discrepency in versions caused the problem?

You learned me something (never needed nor noticed): using the X and Y arguments on mouseevents in listbox.

Well, you can thank Tim. His comment regarding BoundColumn above drove me to figure out if there was a way to change this property at runtime based on WHERE the user clicked insde of the listbox. Not sure what else I might this method for...

Do you know about this syntax?
Code:
Me.ListBox1.List = Range("List_1:List_2").Value

Yup. I only used the loop syntax in the second userform because I copied/pasted from the original userform where the above syntax was not applicable... I would have changed it to the simpler form when cleaning up the code for final implementation.

perhaps overkill, but in case you change your columnwidths
Code:
Dim w As Variant
w = ListBox1.ColumnWidths
w = Left(w, InStr(w, " ") - 1)

If X < w Then

Most excellent point. I'm not sure if I would have thought to make that change before final implementation... but I'll keep it in mind for the future.

1.
I've spent playing quite some time: I would choose for a button
"single or double selection" and switch userforms (or multipage). (Synchronising the listboxes would not be a problem for you.)

2.
NOT GOOD but just to show my "thinking process"
After playing with the idea of a vertical label at the left: when you click there it means you select both items: a hidden "2 column listbox" would replace the two slaved ones.

There is never a case where the user will select a row of data. He will only ever select a single member. The idea is that these two lists represent a Plug and a Socket that mate together (like plugging a toaster into a wall socket). Some of the members are so obscure in their naming convention that a person is likely to remember one member of a pair, but not it's mate, hence, I want the two lists to be synchronized. My thought is that a person will think to himself "I want the Plug that will fit into a D38999\AB25L98 receptacle... oh, I scroll down to this member in the right hand list, and lo and behold, the plug that appears next to it in th eleft-hand list is an NATC\NM63P22... now I select that, and hit okay."

3.
revolutionary
instead of a listbox use a number of labels (2 columns, 10 rows)

Now that's a neat idea! Hadn't even thought about it. Yes, I can see a class module making the coding a little simpler. Though I am not sure that will be much cleaner than the last bit of coding I posted above. Again, since I don't ever need to select two members of the list, some of the functionality you describe becomes moot. But I would still need to capture the Arrow Keys to navigate from label to label, so the coding for that would be similar to what i have above... though even that would be simplified by using an intelligent naming convention for the labels, along with a class module.

if this sounds reasonable, I would be glad to try out

I'll play with this, and let you know if I get stuck or need help. I'd kinda like to try it out on my own: I'm more likely to learn more that way, and I may even see some possibilities that I wouldn't otherwise.

Thanx again Erik. As ever, your observations and suggestions are insightful and thought -provoking

 

[erik.van.geit]

thanks for your comments

since I don't ever need to select two members of the list

I totally misunderstood that part: so wonder why you are not statisfied with the slaved listboxes and arrow-tricks
(perhaps still missing a detail )

have a nice play with the labels

warm regards (sun is abundant last days: we're breaking records, I think),
Erik

 

[hatman]

I totally misunderstood that part: so wonder why you are not statisfied with the slaved listboxes and arrow-tricks
(perhaps still missing a detail )

No problem. When I asked the question in the first place, I had absolutely no idea how to do the arrow tricks, and was hoping for some help in that respect. Through the discussion on this thread, I developed a solution that was marginally workable, using the Mouse Hooks and Ontime... which you pointed out (and I agreed) was very unstable. Your nudge toward the Spreadsheet Control was what indirectly caused me to re-examine the KeyDown event rather than the KeyPress event, and finally resulted in the latest Arrow Tricks, as you decribe them. I am VERY pleased with this solution, and will probably stay with it. However, having been introduced to the Spreadsheet Control, I was interested in learning more about it.

have a nice play with the labels

That's primarily all it is: PLaying. I'm intrigued with the idea, and it may yield some interesting results... but probably not worth spending too much time on.

warm regards (sun is abundant last days: we're breaking records, I think),

Also had some record breaking temperatures earlier this week (following on the heels of record rain the previous week, and record snow the week before that). Back to rain this evening... Once again proving the old saying we have here in New England: "If you don't like the weather, wait a minute."