VBA: Dynamically change the .ListFillRange of a ComboBox
VBA: Dynamically change the .ListFillRange of a ComboBox
(OP)
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").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 <><
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").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 <><





RE: VBA: Dynamically change the .ListFillRange of a ComboBox
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
RE: VBA: Dynamically change the .ListFillRange of a ComboBox
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 Auto_Open
ThisWorkbook.Sheets("Demographics").Activate
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(lxToRight).Select
lnLastProv = Selection.Row
ThisWorkbook.Sheets("Interface").Activate
ActiveSheet.Shapes("cboFacility").ControlFormat.ListFillRange = _
"Demographics!$N$2:$N$" + Trim(Str(lnLastProv))
Range("A2").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 <><