Trying to understand how Combo and List boxes work

[richard12]

I have a listbox and combobox using Excel 2016. The listbox and combobox show 5 columns and I can select a row and the last name is placed in a cell (using LinkedCell).

1. For some reason, the combobox does not show all the columns, as the listbox does, until I click on the down arrow. I can make a selection and then the row showing only shows the last name (first column) that was selected. Is there a reason why the combobox does not display the column data for all columns all the time as the listbox does.

2. The "column headings" are not table headings. Some old comments show it is not possible to have table headings where the column numbers show (used ColumnHeads=True). Can table headings (row 1) be substituted for the column headings?

3. Is there a way to "select" the rest of the data in the row and place them into worksheet cells? I would like to have it work as the "LinkedCell" does, click on the row and have all data in the row clicked placed in selected cells.

Thanks,

Richard

 

[Worf]

1. Combo boxes were designed that way. Would you like a VBA workaround to display all columns?
2. I did not understand what you want to do. If the list fill range is C61:G68 and column heads are set to true, then headers will be populated from C60:G60. On the other hand, column heads can be false and only the data range is displayed. Please explain what you need. Are all your data on worksheet ranges?
3. The code below transfers the whole row to a worksheet range.
4. Are your controls inside a user form or on a worksheet?

' worksheet module
Private Sub ComboBox4_Change()
Dim r As Range, i%, cb
Set cb = Me.ComboBox4
Set r = Me.[j70]                                ' starting cell
For i = 0 To cb.ColumnCount - 1
    r.Offset(, i) = cb.List(cb.ListIndex, i)    ' transfer to sheet
Next
End Sub

 

[richard12]

Worf,

Thanks. I tried the code you sent and part of it works.

"Set cb = Me.ComboBox4" works and shows the selected employee. The group in the combobox are Columns B-F. It looks like: Smith, Sam, 05/18/2018, 56303, 55591 The commas represent separate cells. What I would like to do is copy that group and paste it to separate cells starting at U5 since there is other data that is not relevant in columns F-S. I would like to have the selected combobox data placed in U5-Y5.

The combobox is ActiveX and placed on the worksheet.

The code below "Set cb..." may run, but I could not find any results. Hovering over r shows the same as cb. .


Richard

1. Combo boxes were designed that way. Would you like a VBA workaround to display all columns?
2. I did not understand what you want to do. If the list fill range is C61:G68 and column heads are set to true, then headers will be populated from C60:G60. On the other hand, column heads can be false and only the data range is displayed. Please explain what you need. Are all your data on worksheet ranges?
3. The code below transfers the whole row to a worksheet range.
4. Are your controls inside a user form or on a worksheet?

' worksheet module
Private Sub ComboBox4_Change()
Dim r As Range, i%, cb
Set cb = Me.ComboBox4
Set r = Me.[j70]                                ' starting cell
For i = 0 To cb.ColumnCount - 1
    r.Offset(, i) = cb.List(cb.ListIndex, i)    ' transfer to sheet
Next
End Sub

 

[Worf]

What happens when you run this one?

' worksheet module
Private Sub ComboBox4_Change()
Dim r As Range, i%, cb
Set cb = Me.ComboBox4
MsgBox "Control name is " & cb.Name
Set r = Me.[u5]                                 ' starting cell
MsgBox "Copying to " & r.Parent.Name & "!" & r.Address
For i = 0 To cb.ColumnCount - 1
    r.Offset(, i) = cb.List(cb.ListIndex, i)    ' transfer to sheet
Next
MsgBox "Completed with i=" & i
End Sub

 

[richard12]

Worf,

WOW! Your code worked perfectly without change. Thank you so much. I also better understand how the selection gets to the starting cell. I really appreciate your help.

Richard

What happens when you run this one?

' worksheet module
Private Sub ComboBox4_Change()
Dim r As Range, i%, cb
Set cb = Me.ComboBox4
MsgBox "Control name is " & cb.Name
Set r = Me.[u5]                                 ' starting cell
MsgBox "Copying to " & r.Parent.Name & "!" & r.Address
For i = 0 To cb.ColumnCount - 1
    r.Offset(, i) = cb.List(cb.ListIndex, i)    ' transfer to sheet
Next
MsgBox "Completed with i=" & i
End Sub

 

[Worf]

You are welcome and thanks for the feedback.