Copying Combo Boxes

  • Thread starter Thread starter Wes
  • Start date Start date
W

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
 
If you have a Row# with the ComboBox that is Generic enough, just Copy and
Paste it Downward, then modify each as required.
 
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
 
Back
Top