VBA: Dynamically change the .ListFillRange of a ComboBox

[RHeilman]

I have a combo box in a worksheet that displays the names and numbers of a list of healthcare providers. I initially typed the range into the properties dialog for that combobox. Whenever new providers are added to the provider list, I need a macro to determine the new range and programmatically update the range in the properties of the combobox.

By recording the actions I found that...

ActiveSheet.DropDowns.Add(239.25, 13.5, 144.75, 24.75).Select

...creates and selects the combobox.

ActiveSheet.Shapes("drop down 1&quot.Select

...selects an existing combobox, after which...

Selection.ListFillRange = "$B$1:$B$4"

...updates the range.

The problem is that modifying and executing recorded code is seldom working for me. Does anyone have experience with how to reference the .ListFillRange property (or property that serves the same function) of a combobox?

Thanks,
Ray <><

 

[Tarwn]

You could do something like this in the AutoOpen function:

    Dim rowCtr As Integer   'will be used to find the last row
    Dim column As Integer   'used to easily change the column instead of editing the code
    
    column = 1  'Set the column the data is in
    rowCtr = 1  'Set the row to start with
    'Loop through the rows to find first blank row
    Do Until Len(Cells(rowCtr, column).Text) = 0
        rowCtr = rowCtr + 1
    Loop
    rowCtr = rowCtr-1   'subtract 1 to return to last filled cell

    'Now change the range on the combo box
    cboSampleList.ListFillRange = "A1:A" & rowCtr

You would have to change the name of the combo box and the row and columns, obviously, but it should work. It will basically loop through the rows in th specified column until it reaches a blank row, then it will change the list fill range to the new range. This should work relatively fast, ie not noticeable.

-Tarwn

 

[RHeilman]

Thanks Tarwn,

Much of that is what I was doing to find the last row of the range. The problem was was in the syntax of the code with reference to the objects that I was using. After further study, the code below works.

Sub Autpen

ThisWorkbook.Sheets(&quot;Demographics&quot.Activate
Range(&quot;A1&quot.Select
Selection.End(xlDown).Select
Selection.End(lxToRight).Select
lnLastProv = Selection.Row

ThisWorkbook.Sheets(&quot;Interface&quot.Activate
ActiveSheet.Shapes(&quot;cboFacility&quot.ControlFormat.ListFillRange = _
&quot;Demographics!$N$2:$N$&quot; + Trim(Str(lnLastProv))
Range(&quot;A2&quot.Select

End Sub

By recording what I wanted to do, I found the object reference to the existing ComboBox was a shape. However, since there are many kinds of shapes, I needed to refer to the ControlFormat object as the vehicle to set the ListFillRange property of this specific kind of shape.

Since writing the above code, I'm now defining/redefining range names that refer to the area I want in the ListFillRange. Instead of building the range reference, I just define the name and place that static name text in the ListFillRange.

Thanks again,
Ray <><