Search code examples
excelvbadropdownbox

fill multiple excel dropdowns in a loop

I have a for loop which creates multiple dropdowns in different parts of my excel sheet. However i am having a hard time filling these with a list. The list should be obtained from the sheets in ws. I guess the problem here is that, when naming my combo box, it doesnt get assigned as an object. Here is the code i have. Anybody have an idea on how to do this? Thanks in advance.

Private Sub CommandButton1_Click()

Set ws = Sheets(Array("rpi301", "rpi302", "rpi303", "rpi304", "rpi305"))

For Each Sh In ws
    Set Cell = Range(Sh.Range("B8").Value & CStr(Sh.Range("B9").Value))
    With Cell
        ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height).Name = "Combo"
        ActiveSheet.Shapes("Combo").ControlFormat.ListFillRange = Sh.Range("B2:B7")
    End With
Next 
End Sub
Solution

  • Code is right. But the problem what naming the dropdowns. They should have unique names.