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 Range.Given a simple UserForm featuring some ComboBox1 control that I'd want to populate from a ListObject on Sheet1, the calling/client code looks like this:Option ExplicitSub Test()    With New UserForm1        .PopulateFromList Sheet1.ListObjects(1)        .Show vbModal    End WithEnd Sub(thanks to Thomas Inzina for helping with the column headings)...the Change handler in the form's code is just to illustrate that the hidden ID column is effectively being used as the Combobox.Value:Private Sub ComboBox1_Change()    If Not IsNull(ComboBox1.Value) Then Debug.Print ComboBox1.Value, ComboBox1.TextEnd SubAnd it works! 3            Star Wars 1            Lord of the RingsHere'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 ExplicitPublic 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 WithEnd SubPublic 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 WithEnd SubPrivate 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 FunctionIs there any way to clean this up? The .List assignment in PopulateFromArray seems particularly painful.
Populating a ComboBox from a Range
vba;excel