dropdown control

[Walmir]

ActiveWorkbook.Worksheets("model").Shapes("selector").DropDown =
Range("config!$list")
I would like to use in vba a command that could define dinamically the data
of a dropdown box, something like what is above (but that works!).
Anyone can help me?

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myRng As Range

With Worksheets("Sheet1") '<-- where the data is
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

'If it's a dropdown from the Forms toolbar:
With Worksheets("Sheet2") '<-- where the controls are
.DropDowns("Drop down 1").ListFillRange = myRng.Address(external:=True)
End With


'If it's a combobox from the control toolbox toolbar
With Worksheets("sheet2")
.OLEObjects("combobox1").ListFillRange = myRng.Address(external:=True)
End With

End Sub

 

[Walmir]

Dave,
in my case I used a dropdown from the Forms toolbar, but I didn't want it to
have blank rows. That was my problem that persisted.
Thank you very much for your help.
In fact I found another solution:

For j = 1 To numrows
Range("sheet2!A" & j) = "Estrat " & j
Next j
ActiveWorkbook.Names.Add "list", Range("sheet2!A1:A" & numrows)
ActiveWorkbook.Worksheets("sheet1").Shapes"dropdown1").ControlFormat.ListFillRange = "list"