Create mutiple combo box form controls using Macro

[booo]

Hi,

I am faced with a weird situation. I have 500 lines of records and each of the line has to be decided upon a particular disposition. So I have to have a combo box control with options to choose from for each of the lines. To do it for 500 lines is a tedious task. Can someone help me with a macro to create 500 combo box controls one below the other in successive cells on the click of one macro?.

I was able to create one, but I am not sure if the attributes could be incremented using a loop to get to the next cell. Kindly help.

Thanks,
Booo

[Mervil]

You can try something like this:

Sub ComboCreate()

Application.ScreenUpdating = False
Dim boxnumber As Integer
For boxnumber = 0 To 499
    Worksheets("Sheet1").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=1, Top:=15 * boxnumber, Width:=97.5, Height:=16.5).Select
Next boxnumber
Application.ScreenUpdating = True

End Sub

Then, you can use a For Each loop to do stuff to all those combo objects, like so:

Sub ComboDoStuff()

Dim obj As OLEObject
For Each obj In Worksheets("Sheet1").OLEObjects
    obj.ListFillRange = "Sheet1!D1:D20"
    obj.Placement = 2
    'obj.Delete
Next obj

End Sub