Retrieve a single value from a MultiColumn ListBox

[Bill Hamilton]

I have implemented many listboxes with Excel VBA but all of them have been single columns of data. I now have a situation where I have a list of over 70 items and I thought I'd help the user out a bit with the scrolling up and down the list to find the one(s) they want (even though the items will be in alphabetical order) by displaying the items in a multi-column listbox. On doing an experiment and a bit of googling I now see that if the user selects an item from, say, a 3-column list then what gets presented to the code are the three values in the row with the selected item.

Is there any way of determining the single item that the user actually selected? I don't see the point of having multi-column listboxes if not.

 

[Anthony47]

The selection returns the line, not the cell.
If you still wish to use a multicolumn control then you need to develop some tricks to identify the column within the line.
For example:
-add OptionButtons on top of each of the columns
-use the ListBox1_MouseDown (for example) to step through the OptionButtons and mark which column need to be selected

On a 3 columns listbox, with 3 optionboxes named OB1, OB2 and OB3, the following code do the trick:

Dim OBNum As Long     'On top of the module


Private Sub ListBox1_Click()
Me.Controls("OB1").Value = 1
OBNum = 1
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, OBNum - 1)
End Sub



Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Debug.Print X, Y
OBNum = OBNum + 1
If OBNum > 3 Then OBNum = 1
Me.Controls("OB" & OBNum).Value = 1
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, OBNum - 1)
End Sub

This need to be copied at the beginning of the userform vba module, so that Dim OBNum be on top of the page to make the variable common to all the code in the page

Adapt for the names of the controls and the number of columns
Select the line, then click on the line until the OptionBox on top of the desired column is selected
In the example, I use also a TextBox1 to show which is the current selection; this is mainly for debugging purpose, so remove the lines that write TextBox1 if you prefer not using it.

At the end, use OBNum to select the column within the selected line, as I do with the instruction Me.ListBox1.List(Me.ListBox1.ListIndex, OBNum - 1)
See the attached image


Another method that require a little bit more tuning exploits the X coordinate returned by the MouseDown event.
For example:

Dim OBNum As Long     'On top of the module


Private Sub ListBox1_Click()
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, OBNum - 1)    'show selected cell
End Sub


Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim WArr() As Integer, OBX, CW As Integer
'
ReDim WArr(1 To Me.ListBox1.ColumnCount)
CW = Me.ListBox1.Width / Me.ListBox1.ColumnCount
For I = 1 To UBound(WArr)               'Create list with X for each column
    WArr(I) = CW * (I - 1)
Next I
OBNum = Application.Match(X, WArr, True)  'Identify the column
Me.Controls("OB" & OBNum).Value = 1       'Select OptionButton
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, OBNum - 1)      'show selected cell, optionally
Debug.Print OBNum, Timer, X, WArr(OBNum)
End Sub

In this way the column will be identified by examining the X in the MouseDown event; the optionbutton associated with the column is set to show which is the selected column; additionally TextBox1 shows wich is the selected value
But this will work only if the listbox is shown in its full width

Hope you can pick some good suggestions for your project
(myFile: D:\DDownloads\[MULTI_C401155.xlsm]OutlookGFS)

 

[Bill Hamilton]

Hi Anthony,

Wow, not one but two solutions!

It took me a while to figure out that I needed to specify fmMultiSelectMulti instead of fmMultiSelectSingle, and that's logical when I thought about it.

I've given both ideas a shot and I'll definitely make use of one of them.

Thank you very much.

Bill

 

[Anthony47]

It took me a while to figure out that I needed to specify fmMultiSelectMulti instead of fmMultiSelectSingle, and that's logical when I thought about it

No, fmMultiSelectMulti must not be enabled; you probably associated the "option button style" to this option (see attached image), but it was only a choice to match the vertical "column" OptionButtons with a "row" pseudo optionbutton. Technically that is obtained by setting within the listbox properties ListStyle=1 fmListStyleOption and does not imply "multy select" (and indeed "optionbuttons" imply some mutually exclusive options)

A final consideration: in my tests in some circumstances the first click in the listbox resulted is a runtime error; it would be better to set a default listbox choice within the userform_initialize; for example:

Private Sub UserForm_Initialize()
'
Me.ListBox1.ListIndex = 0
Me.OB1.Value = 1
OBNum = 1
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, OBNum - 1)
'
' Other initialize commands
'
End Sub

This would select the first line and the first column when the userform is initially showed