MultiColumn ComboBox Display

[Minitman]

Greetings,

I have a UserForm ComboBox with a RowSource of 27 rows and 20 columns.

How can I get only the values in the 4th column to be displayed in the
drop down window? Is this possible?

Anyone have any ideas, thoughts or links related to this question?

Any help will be appreciated.

-Minitman

 

[Tim Zych]

One way is to set the ColumnCount to 4 and the ColumnWidths to 0;0;0

 

[Minitman]

Hey Tim,

Thanks for the reply.

Question: By changing the column count, won't that be limiting the
number of columns in the row source?

I can see the column width should effectively hide the first three
columns, that is a real help!!!

I was wondering, if I made the 4th column as wide as the ComboBox
without limiting the number of columns, wouldn't that, in effect, give
me the appearance of only the 4th column appearing in the ComboBox?

Again, thanks for the pointer on the column widths.

-Minitman

 

[Tim Zych]

ColumnCount limits only the display and you still have access to the hidden
columns. You can see by selecting a value in the combo, then running:

MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 15)

which will display the 16th column of the selected row. Does not matter that
ColumnCount = 4.

I was wondering, if I made the 4th column as wide as the ComboBox
without limiting the number of columns, wouldn't that, in effect, give
me the appearance of only the 4th column appearing in the ComboBox?

I don't think so. To achieve that effect, you might need to resort to
something like:

Private Sub UserForm_Initialize()
Dim i As Integer, ColWidth As String
For i = 1 To Range(Me.ComboBox1.RowSource).Columns.Count
If i = 4 Then
ColWidth = ColWidth & Me.ComboBox1.Width & ";"
Else
ColWidth = ColWidth & 0 & ";"
End If
Next
ColWidth = Left(ColWidth, Len(ColWidth) - 1)
Me.ComboBox1.ColumnWidths = ColWidth
End Sub

IMO easier to do it the other way.

 

[Minitman]

Cool!!!

That was one of the gaps in my knowledge of vba. With this I can load
my TextBoxes from the ComboBox list & display only the column I need
in the ComboBox.

Thank you, that helps a lot.

-Minitman