Copying Combo Boxes

[Wes]

I'm creating a form that has 120 rows. Each row will
have the same functionality. There will be a combo box
at the beginning of each row. This combo box will run a
macro that will adjust the formulas for the row it is on.

My Problem:
Unlike a formula in a cell, relative addressing does not
seem to work with a Combo box. I don't want to create 120
combo boxes one by one so how do I automate this
procedure?

Thanks

 

[JMay]

If you have a Row# with the ComboBox that is Generic enough, just Copy and
Paste it Downward, then modify each as required.

 

[Debra Dalgleish]

The following macro will add combo boxes from the Forms toolbar, linked
to the row in which they're placed.

Sub AddCombos()
Dim i As Integer
Dim cb As Object

With ActiveSheet
.Rows("1:120").RowHeight = 22
.Columns("A:A").ColumnWidth = 22
For i = 1 To 120
Set cb = .Shapes.AddFormControl _
(xlDropDown, 10, Cells(i, 1).Top + 4, 100, 15)
cb.ControlFormat.LinkedCell = "A" & i
cb.ControlFormat.ListFillRange = "$F$1:$F$12"
Next
End With

End Sub