×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA: Dynamically change the .ListFillRange of a ComboBox

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

RE: VBA: Dynamically change the .ListFillRange of a ComboBox

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

RE: VBA: Dynamically change the .ListFillRange of a ComboBox

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources