VBA: Dynamically updating the .ListFillRange of a ComboBox...

[rheilman]

I have a worksheet that contains a ComboBox (named "cboFacility&quot. Based upon the property dialog for this ComboBox, the Input Range is $N$2:$N$105. When a user adds rows of information to this range, I'd like the macro to programatically update the Input Range.

Through recording a macro on a test sheet, I see that...

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

...creates and selects a Combobox and...

Selection.ListFillRange = "$N$2:$N$106"

...sets the Input Range.

ActiveSheet.Shapes("cboFacility&quot.Select

...selects an existing ComboBox. When I try to modify and apply this recorded code to my real macro operation, it tells me that the object does not support this property. Furthermore, when I look in the object browser, I cannot find DropDown even listed as an object. Any clues on how to programmatically update the .ListFillRange?

Thanks,
Ray <><

 

[RobBroekhuis]

Hi Ray,
Before getting too far, please first tell us which toolbar you used to put the combobox on your sheet: &quot;forms&quot; or &quot;control toolbox&quot;? They result in two very different objects, although they are both basically comboboxes.
Rob

 

[rheilman]

Thanks for responding, Rob. I've already somewhat solved this one. To answer your question, the ComboBox came from the Forms toolbar. To update the recorded code above, the following code works.

ActiveSheet.Shapes(&quot;cboFacility&quot.ControlFormat.ListFillRange = _
&quot;Demographics!$N$2:$N$&quot; + Trim(Str(lcLastProv))

In explanation, since there are so many different members of shapes, the ControlFormat object facilitates setting the appropriate properties for the shape in question. In my app, lcLastProv is the row number of the last row of data that feeds the ComboBox.

 

[RobBroekhuis]

Sounds like you've got it covered. Just FYI, you can do away with the trim(str() bit, and just use


&quot;$N$2:$N$&quot; & lcLastProv

Rob

 

[rheilman]

Hi Rob,

Based on my deceptive Hungarian notation, you are completely correct. It probably should read lnLastProv, since I'm getting it from the .Row property and it returns Long. Those who live by Hungarian notation also die by it.

Ray <><