How do I copy a combobox to many cells with relative reference?

[Guest]

I have made a combobox form control and want to copy it to multiple cells,
the problem being the cell link reference is absolute by default and not
relative. Is there a quick way to copy comboboxes and make them relative
references instead of absolute?

 

[Dave Peterson]

I don't think so. Have you thought of using data|validation instead?

If you can't use Data|Validation...

When I want multiple dropdowns from the Forms toolbar, I'll use a macro.

If you want to try:

Option Explicit
Sub testme1()
Dim myDropDown As DropDown
Dim myRng As Range
Dim myCell As Range
Dim myList As Range

With Worksheets("Sheet2")
Set myList = .Range("a1:A10")
End With

With Worksheets("sheet1")
.DropDowns.Delete 'nice for testing???

Set myRng = .Range("a1:a4,c9")

For Each myCell In myRng.Cells
With myCell
.NumberFormat = ";;;" 'hide the value in the cell
Set myDropDown = .Parent.DropDowns.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
End With

With myDropDown
.LinkedCell = myCell.Address(external:=True)
.ListFillRange = myList.Address(external:=True)
End With
Next myCell
End With

End Sub

If you used the combobox from the control toolbox toolbar:

Option Explicit
Sub testme()
Dim OLEObj As OLEObject
Dim myRng As Range
Dim myCell As Range
Dim myList As Range

With Worksheets("Sheet2")
Set myList = .Range("a1:A10")
End With

With Worksheets("sheet1")
For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
OLEObj.Delete
End If
Next OLEObj

Set myRng = .Range("a1:a4,c9")
For Each myCell In myRng.Cells
With myCell
.NumberFormat = ";;;" 'hide the value in the cell
Set OLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, _
Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
End With

With OLEObj
.LinkedCell = myCell.Address(external:=True)
.ListFillRange = myList.Address(external:=True)
End With
Next myCell
End With

End Sub