vba dynamical filling combo boxes

Question

Here is the set up, (all testing code)

I have a combo box on sheet one called "Devmod"

I have created a module called testing and with in it created the following sub

Sub setcomb()

    Sheet1.Devmod.ListFillRange = Range(Sheets("Device_info").Range("l3"), _
                      Sheets("Device_info").Range("l3").End(xlDown)).Select
End Sub

this runs OK but does not fill the combo box with any data (although it does select the right cells I want)

Now I know I can do this be creating a name range and then assigning this to the combo box.

but what I am trying to do is when the user click a "update button" each combo box is updated with the values entered.

In logical terms this would be

Select the range of cells from the Device_info sheet, Start at cell L3 and work out the last cell used. Assign these values to the combo box.

can I do this with one or two lines of code?

Cheers

DevilWAH

ANSWER My simple function ends up as

    Function setcomblist(wsheet As String, startrng As String, Optional endrng As Variant)

'used to populate comboboxes from a dynamic lenth range taking named sheet and starting cell as argument.
'use: combobox.ListFillRange = setcombolist("sheetname", "startcell")

If IsMissing(endrng) Then

setcomblist = Range(Sheets(wsheet).Range(startrng), Sheets(wsheet).Range(startrng).End(xlDown)).Address(External:=True)

Else

setcomblist = Range(Sheets(wsheet).Range(startrng), Sheets(wsheet).Range(endrng)).Address(External:=True)
End If

End Function

Thanks agin for the help people

mischab1 mischab1 · Accepted Answer · 2012-02-10T02:19:17

ListFillRange is expecting a range address as a string. If you want to both select the range and assign it to the combo box you have to do that as two separate lines of code. Also, because your range of cells is on another worksheet, you need to give the range's external address. (Otherwise the combo box will think you mean the range on it's worksheet.)

Sheet1.Devmod.ListFillRange = Range(Sheets("Device_info").Range("l3"), _
                  Sheets("Device_info").Range("l3").End(xlDown)).Address(External:=True)

For clarity, I'd rewrite it as follows.

Sub setcomb()
    Dim rng as Range
    Set rng = Sheets("Device_info").Range("L3")
    Sheet1.Devmod.ListFillRange = Range(rng, rng.End(xlDown)).Address(External:=True)
End Sub

On the other hand, you can also do this with a dynamic named range. I don't recall how to create one off the top of my head, I'll look it up and add it later.

For a dynamic named range, enter something like this in Refers To.

=OFFSET(Device_info!$L$3,0,0,COUNTA(Device_info!$L$3:$L$303),1)

The COUNTA function needs to include the max number of rows you want to allow. The example above creates a dynamic named range that can grow up to 300 rows. And as my trusty 'Excel Hacks' book says:

When defining the range for COUNTA, resist the temptation to include an entire column of data so you do not force the COUNTA function to count potentially thousands of unnecessary cells.

vba dynamical filling combo boxes

2 Answers