Populating a ComboBox from a Range

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 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)

...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.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(

I'm not sure I'd compare the .width properties of a control and a range - hear me out.

Let's start with a table of relevant measurements

Inch       .5
cm         1.27
Pixel      48
Point      36
Characters 5.38

With a Range

In the excel UI you see two numbers - characters and pixels when manually adjusting width.

Characters being the number of characters with the font (I'm using default here- calibri 11) that can fit in the width of the range (yes, that's a ridiculous measurement unit).

In the Format menu for a range, the Column Width is also characters.

In VBA

Range.Width = points (Variant Double)
Range.ColumnWidth = characters (Variant Double)

.width here is giving us points which then must be divided by .75 to get to pixels to match the UI.

With a Form

The properties of an active x combobox on a form are

ComboBox1.ColumnWidths = points (String)
ComboBox1.Width = pixels (Single)
ComboBox1.ListWidth = points (String)

In the code

.ColumnWidths = GetColumnWidths(source.Range)
.ListWidth = IIf(ComboBox1.Width > source.Range.Width, ComboBox1.Width, source.Range.Width)

Essentially you set .ColumnWidths (String in points) with a String representing points. Nice.

Then you compare pixels to points and set ListWidth (String in points) with either pixels (as Single) or points (Variant Double).

There's no avoiding this mess, so I would break out another function that returns a String of points.

Yes, it's needlessly complicated to do so, unless someone is trying to debug some more complex version of this or you're like me and you hate that excel has done this.

Dim myControlBox As Control
    Set myControlBox = Me.ComboBox1
    With myControlBox
        .ColumnCount = source.Columns.Count
        .ColumnWidths = GetColumnWidths(source)
        .ListWidth = GetListWidth(source, myControlBox)
    End With

Private Function GetListWidth(ByVal source As Range, ByVal myControlBox As Control) As String
    If myControl.Width > source.Width Then
        GetListWidth = myControl.Width * 0.75
        Exit Function
    Else: GetListWidth = source.Width
End Function

This answer took me longer to write that I'm willing to admit.

StackExchange Code Review Q#140995, answer score: 3