Selecting any single value in any column in a multicolumn combobox

[chazrab]

This code only selects the value on the selected row of the first column

Private Sub CommandButton1_Click()
TextBox1.Value = ComboBox1.Value
End Sub

If I click on any book in any column the button code should put that clicked book value in Textbox1 - not the first value on that row, which is what its doing.
I'm sure this has come up at some point before. chatGPT does a poor job of helping, just copying code from Stackoverflow.
Image best explains dilemma. Seems doable.
Thanks for anyone's help.
cr

 

[Domenic]

So, if I understood you correctly, if you click on Mathew on that same row that's highlighted, you want Mathew to be selected, correct?

If so, I have a possible solution. However, you would have to use a ListBox instead of a ComboBox. Would this be acceptable?

If so, did you specifically set the column widths for your ComboBox? If so, can you please provide them?

 

[chazrab]

Hi Domenic - getting to this late - sorry - a Listbox is fine. And you're exactly correct. If I click on Matthew, Matthew should appear in Textbox1 and so on. I didn't set the column widths. I did try this with a listbox and got the same result. Image below.
Curious as to how this could be made to work.

Thanks for all your help.
cr

 

[Domenic]

The following code will allow you to double-click any value, and it will automatically place the selected value in the textbox...

Option Explicit

Dim selectedColumn As Long

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.ListBox1
        Me.TextBox1.Value = .List(.ListIndex, selectedColumn)
    End With
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        selectedColumn = (X \ (Me.ListBox1.Width / Me.ListBox1.ColumnCount))
    End If
End Sub

Private Sub UserForm_Initialize()

    Dim columnWidths As String
    Dim columnIndex As Long
   
    columnWidths = ""
    With Me.ListBox1
        .ColumnCount = 5
        For columnIndex = 1 To .ColumnCount
            columnWidths = columnWidths & (.Width / .ColumnCount) & ";"
        Next columnIndex
        .columnWidths = columnWidths
        .List = Range("A2:E5").Value 'change as desired
    End With
   
End Sub

Hope this helps!

 

[chazrab]

The following code will allow you to double-click any value, and it will automatically place the selected value in the textbox...

Option Explicit

Dim selectedColumn As Long

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.ListBox1
        Me.TextBox1.Value = .List(.ListIndex, selectedColumn)
    End With
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        selectedColumn = (X \ (Me.ListBox1.Width / Me.ListBox1.ColumnCount))
    End If
End Sub

Private Sub UserForm_Initialize()

    Dim columnWidths As String
    Dim columnIndex As Long
  
    columnWidths = ""
    With Me.ListBox1
        .ColumnCount = 5
        For columnIndex = 1 To .ColumnCount
            columnWidths = columnWidths & (.Width / .ColumnCount) & ";"
        Next columnIndex
        .columnWidths = columnWidths
        .List = Range("A2:E5").Value 'change as desired
    End With
  
End Sub

Hope this helps!

Getting this message as in image below. Never have seen a "Permission denied" message. Copied your code as below. Used a new separate userform to put Listbox1 and your code:

Option Explicit
Dim selectedColumn As Long

Private Sub cmdOK_Click()
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.ListBox1
        MAINWINDOW2.TextBox11.Value = .List(.ListIndex, selectedColumn) 'MAINWINDOW2 is the calling form or main userform
    End With

End Sub


Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        selectedColumn = (X \ (Me.ListBox1.Width / Me.ListBox1.ColumnCount))
    End If
End Sub



Private Sub UserForm_Initialize()
 Dim columnWidths As String
    Dim columnIndex As Long
   
    columnWidths = ""
    With Me.ListBox1
        .ColumnCount = 5
        For columnIndex = 1 To .ColumnCount
            columnWidths = columnWidths & (.Width / .ColumnCount) & ";"
        Next columnIndex
        .columnWidths = columnWidths
        .List = Range("A1:E16").Value 'change as desired
    End With

End Sub

.List = Range("A1:E16").Value 'change as desired - This line doesn't tell me where this range is located. I assume its on a sheet
when I reference the sheet and range = Sheets("BIBLEBOOKS").Range("A1:E16") I still get the same "Permission denied" message.

cr

 

[Domenic]

This line was just an example...

.List = Range("A2:E5").Value 'change as desired

You're already assiging the data using the RowSource property, so you can delete that line of code. When using the List property to assign values, you'll get an error if the RowSource property is already set.

 

[chazrab]

This line was just an example...

.List = Range("A2:E5").Value 'change as desired

You're already assiging the data using the RowSource property, so you can delete that line of code. When using the List property to assign values, you'll get an error if the RowSource property is already set.

After deleting the code line, It now works great - is there any way to get rid of that horizontal scroll bar that keeps appearing?
No matter how much I adjust the Listbox width, it always appears.

 

[Domenic]

Try the following instead...

Option Explicit

Dim selectedColumn As Long
Dim widenListBoxBy As Long

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.ListBox1
        Me.TextBox1.Value = .List(.ListIndex, selectedColumn)
    End With
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        selectedColumn = (X \ ((Me.ListBox1.Width - widenListBoxBy) / Me.ListBox1.ColumnCount))
        With Me.ListBox1
            If selectedColumn > .ColumnCount - 1 Then
                selectedColumn = .ColumnCount - 1
            End If
        End With
    End If
End Sub

Private Sub UserForm_Initialize()

    Dim columnWidths As String
    Dim columnIndex As Long
   
    widenListBoxBy = 10
   
    columnWidths = ""
    With Me.ListBox1
        .ColumnCount = 5
        For columnIndex = 1 To .ColumnCount
            columnWidths = columnWidths & (.Width / .ColumnCount) & ";"
        Next columnIndex
        .columnWidths = columnWidths
        .Width = .Width + widenListBoxBy
    End With
   
End Sub

Hope this helps!

 

[chazrab]

Hi Domenic - thanks very much for helping - I copied this code with the changes you made but the horizontal scroll bar still appears

cr

 

[Domenic]

Does it help if you set the ListBox width before setting the columnWidths?

    With Me.ListBox1
        .ColumnCount = 5
        For columnIndex = 1 To .ColumnCount
            columnWidths = columnWidths & (.Width / .ColumnCount) & ";"
        Next columnIndex
        .Width = .Width + widenListBoxBy
        .columnWidths = columnWidths
    End With