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)