I recently had quite a lot of fun answering a Stack Overflow question, and I think I've gone a bit overboard and ended up with a fairly decent way of populating a
ComboBox from a
Given a simple
UserForm featuring some
ComboBox1 control that I'd want to populate from a
Sheet1, the calling/client code looks like this:
Option Explicit Sub Test() With New UserForm1 .PopulateFromList Sheet1.ListObjects(1) .Show vbModal End With End Sub
(thanks to Thomas Inzina for helping with the column headings)
Change handler in the form's code is just to illustrate that the hidden ID column is effectively being used as the
Private Sub ComboBox1_Change() If Not IsNull(ComboBox1.Value) Then Debug.Print ComboBox1.Value, ComboBox1.Text End Sub
And it works!
3 Star Wars 1 Lord of the Rings
Here's the rest of the form's code-behind, the actual code under review - I'm wondering what's the best way to reuse it, I haven't decided whether it's best to extract it into a class module as a utility; with a
WithEvents foo As ComboBox field I could be having a dynamic control there... it's certainly not practical in the code-behind of some random form though.
I'm not very happy with
GetColumnWidths, I'm sure there's a better way to do this. As for the
PopulateFromXxxx methods, ...they're perfect, aren't they? ;-)
Option Explicit Public Sub PopulateFromList(ByVal source As ListObject, Optional ByVal valueColumn As Long = 1, Optional ByVal hasHeader As Boolean = True) With Me.ComboBox1 .ColumnCount = source.Range.Columns.Count .ColumnWidths = GetColumnWidths(source.Range) .ListWidth = IIf(ComboBox1.Width > source.Range.Width, ComboBox1.Width, source.Range.Width) .RowSource = source.Name & "[#Data]" .BoundColumn = valueColumn .ColumnHeads = hasHeader End With End Sub Public Sub PopulateFromArray(ByVal source As Range, Optional ByVal valueColumn As Long = 1, Optional ByVal hasHeader As Boolean = True) With Me.ComboBox1 .ColumnCount = source.Columns.Count .ColumnWidths = GetColumnWidths(source) .ListWidth = IIf(ComboBox1.Width > source.Width, ComboBox1.Width, source.Width) .List = source.Range(source.Rows(IIf(hasHeader, 2, 1)).EntireRow, source.Rows(source.Rows.Count).EntireRow).Value .BoundColumn = valueColumn End With End Sub Private Function GetColumnWidths(ByVal source As Range) As String Dim cols As Long cols = source.Columns.Count Dim widths() ReDim widths(1 To cols) Dim col As Long For col = 1 To cols widths(col) = source(, col).Width Next GetColumnWidths = Join(widths, ",") End Function
Is there any way to clean this up? The
.List assignment in
PopulateFromArray seems particularly painful.