select independent items from multiple columns in listbox/combobox

ricardomadaleno

Board Regular
Joined
Mar 25, 2012
Messages
65
Good afternoon everyone,

I am designing a userform and I have run into a bit of an issue.

I have a listbox with four columns full of data, and i want to select one item without having to select the entire line across the 4 columns.

for instance:
1234
5678
9101112
13141516

<tbody>
</tbody>

Imagine that this is my list box. What I need is to be able to select the number 6 without having to select the entire second line.

is this possible? it doesn't have to be with a listbox or a combobox, I open to all suggestions.

thank you for your help
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

ricardomadaleno

Board Regular
Joined
Mar 25, 2012
Messages
65
Good afternoon Jerry,

Thank you for sharing that, I'm sure I'll have good use for it, but unfortunately, in this specific situation the entries in my listbox are already 93, and they are supposed to keep "growing" over time...

I'm feeding the listbox with a single column dynamic named range; so whatever solution needs to be able to be dynamic as well.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
You can do it by altering the "BoundColumn" as shown below.
You will need to alter the code to take account of the number of columns, Data Range and column widths (which relate to both columnWidths and the "X" value in the Mouse_Down" code)
Code:
Option Explicit
Private [COLOR=Navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=Navy]With[/COLOR] ListBox1
    .ColumnCount = 4
    .ColumnWidths = "20,20,20,20"
    .List = Range("A1:D4").Value
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Private [COLOR=Navy]Sub[/COLOR] listBox1_MouseDown(ByVal Button [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] ByVal Shift [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] ByVal X [COLOR=Navy]As[/COLOR] Single, ByVal Y [COLOR=Navy]As[/COLOR] Single)
[COLOR=Navy]Select[/COLOR] [COLOR=Navy]Case[/COLOR] True
[COLOR=Navy]Case[/COLOR] [COLOR=Navy]Is[/COLOR] = X > 0 And X < 20: ListBox1.BoundColumn = 1
[COLOR=Navy]Case[/COLOR] [COLOR=Navy]Is[/COLOR] = X > 20 And X < 40: ListBox1.BoundColumn = 2
[COLOR=Navy]Case[/COLOR] [COLOR=Navy]Is[/COLOR] = X > 40 And X < 60: ListBox1.BoundColumn = 3
[COLOR=Navy]Case[/COLOR] [COLOR=Navy]Is[/COLOR] = X > 60 And X < 80: ListBox1.BoundColumn = 4
[COLOR=Navy]End[/COLOR] Select
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]


Private [COLOR=Navy]Sub[/COLOR] ListBox1_Click()
Range("F1") = Me.ListBox1.Value
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779

ADVERTISEMENT

A list box can't tell which column the user is choosing.
Instead of a single 4 column list box, you could use 4 single column listbox's (put right next to each other for the visual effect)
One could even write a class module so those four list boxes would act as a single control. (synching the .TopRow property for example).
 

ricardomadaleno

Board Regular
Joined
Mar 25, 2012
Messages
65
Hi Mick, your approach was really cool, and it worked; but unfortunately it still highlights the entire line which would be extremely confusing for my users :( thanks though

Hi mikerickson, i had though of that approach as well, but i couldn't get around the fact that the data range is dynamic, so I wouldn't know how to divide the data through the 4 single column listbox's continuously... dou you know how to do this? Ideally the 4 listboxes would display the data continuously horizontally, but I could work with vertically... As I said, it doesn't even need to be a listbox, if someone else has another solution...

Thank you
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779

ADVERTISEMENT

I'm at work so I can't work on this right now.
How to divide the data?

STYLE A)

The easiest way would result in a display like
1 2 3 4
5 6 7
add two more items and it would look like
1 2 3 4
5 6 7 8
9

STYLE B)

A more complicated coding would be arranged like this.
1 3 5 7
2 4 6
add two more items and see
1 4 6 8
2 5 7 9
3

Which do you prefer?

The data range being dynamic would be no problem.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
This is what I've come up with (for a start).
Put this code into a Class Module named clsQuadListBox.
It will create a custom object with three properties

Columns(index), Count, MultiSelect, ZFlowOrder
Columns will return the listbox associated with that index (0-based)
ZFlowOrder = True will give style A above, False gives style B

two methods, AddItem and Clear
and two Events Change and Click


Code:
' in clsQuadListBox class module

Public WithEvents Column0 As MSForms.ListBox
Public WithEvents Column1 As MSForms.ListBox
Public WithEvents Column2 As MSForms.ListBox
Public WithEvents Column3 As MSForms.ListBox

Public ZFlowOrder As Boolean

Dim clsEventsDisabled As Boolean

Event Change(ColumnIndex As Long)
Event Click(ColumnIndex As Long)

Public Sub AddItem(Item As Variant)
    Dim i As Long, j As Long
    
    If ZFlowOrder Then
        For i = 1 To 3
            With Me.Columns(i)
                If .ListCount < Me.Columns(i - 1).ListCount Then
                    .AddItem CStr(Item)
                    Exit Sub
                End If
            End With
        Next i
        Me.Column0.AddItem CStr(Item)
    Else
        For i = 3 To 1 Step -1
            With Me.Columns(i)
                If .ListCount < Me.Columns(i - 1).ListCount Then
                    Exit For
                End If
            End With
        Next i
        
        If Me.Columns(i).ListCount = 0 Then
            Me.Columns(i).AddItem CStr(Item)
        Else
            For j = i To 2
                Me.Columns(j).AddItem Me.Columns(j + 1).List(0)
                Me.Columns(j + 1).RemoveItem 0
            Next j
            Me.Column3.AddItem CStr(Item)
        End If
    End If
End Sub

Sub Clear()
    With Me
        .Column0.Clear
        .Column1.Clear
        .Column2.Clear
        .Column3.Clear
    End With
End Sub

Property Get Columns(Index As Long) As MSForms.ListBox
    Select Case Index
        Case 0: Set Columns = Column0
        Case 1: Set Columns = Column1
        Case 2: Set Columns = Column2
        Case 3: Set Columns = Column3
    End Select
End Property
 
 Property Get MultiSelect() As fmMultiSelect
    MultiSelect = Me.Column0.MultiSelect
 End Property
 Property Let MultiSelect(newMS As fmMultiSelect)
    With Me
        .Column0.MultiSelect = newMS
        .Column1.MultiSelect = newMS
        .Column2.MultiSelect = newMS
        .Column3.MultiSelect = newMS
    End With
 End Property

Private Sub Class_Initialize()
    ZFlowOrder = True
End Sub

Private Sub Column0_Click()
    If clsEventsDisabled Then Exit Sub
    Dim i As Long
    With Me
        If .MultiSelect = fmMultiSelectSingle Then
            clsEventsDisabled = True
             .Column1.ListIndex = -1
             .Column2.ListIndex = -1
             .Column3.ListIndex = -1
             clsEventsDisabled = False
        End If
    End With
    RaiseEvent Click(0)
End Sub
Private Sub Column1_Click()
    If clsEventsDisabled Then Exit Sub
    Dim i As Long
    With Me
        If .MultiSelect = fmMultiSelectSingle Then
            clsEventsDisabled = True
             .Column0.ListIndex = -1
             .Column2.ListIndex = -1
             .Column3.ListIndex = -1
             clsEventsDisabled = False
        End If
    End With
    RaiseEvent Click(1)
End Sub
Private Sub Column2_Click()
    If clsEventsDisabled Then Exit Sub
    Dim i As Long
    With Me
        If .MultiSelect = fmMultiSelectSingle Then
            clsEventsDisabled = True
             .Column0.ListIndex = -1
             .Column1.ListIndex = -1
             .Column3.ListIndex = -1
             clsEventsDisabled = False
        End If
    End With
    RaiseEvent Click(2)
End Sub
Private Sub Column3_Click()
    If clsEventsDisabled Then Exit Sub
    Dim i As Long
    With Me
        If .MultiSelect = fmMultiSelectSingle Then
            clsEventsDisabled = True
             .Column0.ListIndex = -1
             .Column1.ListIndex = -1
             .Column2.ListIndex = -1
             clsEventsDisabled = False
        End If
    End With
    RaiseEvent Click(3)
End Sub

Private Sub Column0_Change()
    If clsEventsDisabled Then Exit Sub
    RaiseEvent Change(0)
End Sub
Private Sub Column1_Change()
    If clsEventsDisabled Then Exit Sub
    RaiseEvent Change(1)
End Sub
Private Sub Column2_Change()
    If clsEventsDisabled Then Exit Sub
    RaiseEvent Change(2)
End Sub
Private Sub Column3_Change()
    If clsEventsDisabled Then Exit Sub
    RaiseEvent Change(3)
End Sub
This could be used in a user form like this. This code is for a user form with 4 list boxes (one for each column of the data) and four command buttons that drive testing routines.
one button toggles the ZFlowOrder (shown in the uf caption), one Adds many items to the QuadBox, one adds only one item to the quad box, one clears the quad box.
Try clearing the Quad box, change the ZFlowOrder and Add some items to see how the ZFlowOrder changes the order of the items.

Also, note the change event. The ColumnIndex is the (0 based) index of the column that is clicked. If the QuadBox is not multi-select (default) , then one can cleanly identify which row and which column was clicked.

Code:
' in userform code module

Public WithEvents QuadBox As clsQuadListBox

Private Sub UserForm_Initialize()
    Set QuadBox = New clsQuadListBox
    With QuadBox
        Set .Column0 = Me.ListBox1
        Set .Column1 = Me.ListBox2
        Set .Column2 = Me.ListBox3
        Set .Column3 = Me.ListBox4
    End With
    
    With Me
        .Caption = QuadBox.ZFlowOrder
' label testing buttons
        .CommandButton1.Caption = "Toggle ZFlowOrder"
        .CommandButton2.Caption = "Clear"
        .CommandButton3.Caption = "Add Many"
        .CommandButton4.Caption = "Add One"
    End With
End Sub

Rem Testing Routines
Private Sub CommandButton1_Click()
    Rem toggle flow order
    With QuadBox
        .ZFlowOrder = Not .ZFlowOrder
        Me.Caption = "ZFlowOrder = " & .ZFlowOrder
    End With
End Sub

Private Sub CommandButton2_Click()
    QuadBox.Clear
End Sub

Private Sub CommandButton3_Click()
    Rem add many
    Dim i As Long
    With QuadBox
    For i = .Count + 1 To .Count + 9
        .AddItem i
    Next i
    End With
End Sub

Private Sub CommandButton4_Click()
    Rem add one
    With QuadBox
        .AddItem (.Count + 1)
    End With
End Sub

Rem Event Code
Private Sub QuadBox_Click(ColumnIndex As Long)
    With QuadBox
        If .MultiSelect = fmMultiSelectSingle Then
            MsgBox "column " & (ColumnIndex + 1) & ", row" & (.Columns(ColumnIndex).ListIndex + 1)
        Else
            MsgBox "Column " & (ColumnIndex + 1) & " clicked"
        End If
    End With
End Sub
The class uses 0 based column and row indices (to match Excel's style). Note that the output from the change event is 1 based.
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Mike, Very nice example!

You didn't include the Count Property procedure for the Class. I put in a placeholder code for that to test, but I might be incrementing the private mCount value differently than you intended in the AddItem code.

Perhaps because of that the order displayed was Style A regardless whether ZOrder was True or False.

I'm relatively new to Classes that use Events other than the built-in Events so this is a very helpful example. Thanks! :)

EDIT: ZOrder is working fine now- my mistake thinking it was only writing to Style A.
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
This is the code for the count property.
Code:
Property Get Count() As Long
    With Me
        Count = .Column0.ListCount + .Column1.ListCount + .Column2.ListCount + .Column3.ListCount
    End With
End Property
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,654
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top