Retrieve a single value from a MultiColumn ListBox

Bill Hamilton

Board Regular
Joined
Mar 30, 2004
Messages
95
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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:
VBA Code:
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:
VBA Code:
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)
 

Attachments

  • Screenshot 2024-01-22 153637.png
    Screenshot 2024-01-22 153637.png
    23.6 KB · Views: 11
Upvote 0
Solution
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
 
Upvote 0
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:
VBA Code:
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
 

Attachments

  • Screenshot 2024-01-24 003926.png
    Screenshot 2024-01-24 003926.png
    37.6 KB · Views: 11
Upvote 0

Forum statistics

Threads
1,223,152
Messages
6,170,389
Members
452,323
Latest member
GoJones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top