Copying Combo Boxes

[RickGreg]

I am using a series of combo boxes as an input control in a worksheet. Each
combo box toggles between yes/no to control the input on a given row. There
will be close to 100 combo boxes in the sheet (one each in rows 1-100). The
input range will be the same for all combo boxes. However, the Cell Link
will coincide with the row the box sits in.

For example, combo box in Row 9 will determine value of cell D9. Combo box
in Row 10 will control cell D10, and so on.

My question: When I copy/paste the combo boxes, the input range and cell
link entries remain the same. I then have to manually change the Cell Link
for each box.

Is there a way to automate this, or make cell references relative to the
location of the combo box? Looking to save some time and typing! (I create
sheets like this often, so this would save me time now and in the future.)

Thanks in advance.

Excel 11.3.7 (070727)
OS 10.4.10

 

[JE McGimpsey]

My question: When I copy/paste the combo boxes, the input range and cell
link entries remain the same. I then have to manually change the Cell Link
for each box.

Is there a way to automate this, or make cell references relative to the
location of the combo box? Looking to save some time and typing! (I create
sheets like this often, so this would save me time now and in the future.)

one way:

Public Sub SetDropDownLinks()
Dim dd As DropDown
For Each dd In ActiveSheet.DropDowns
With dd
.LinkedCell = .Parent.Cells(.TopLeftCell.Row, "C").Address
End With
Next dd
End Sub

 

[RickGreg]

one way:

Public Sub SetDropDownLinks()
Dim dd As DropDown
For Each dd In ActiveSheet.DropDowns
With dd
.LinkedCell = .Parent.Cells(.TopLeftCell.Row, "C").Address
End With
Next dd
End Sub

Thanks for the quick response. Two follow-ups if I may:

What do I do with your code? Create a new macro and paste it? (I'm a VBA novice!)

Is there a way to adapt your code so it only applies to Selected dropdowns in

a sheet (vs. All dd in sheet)?? I have a few other DD's in this sheet that do
not link to the same column.

All the relevant DDs are in the same column if that helps.

Thanks again!

 

[RickGreg]

one way:

Public Sub SetDropDownLinks()
Dim dd As DropDown
For Each dd In ActiveSheet.DropDowns
With dd
.LinkedCell = .Parent.Cells(.TopLeftCell.Row, "C").Address
End With
Next dd
End Sub

I answered my first question with a quick test creating a macro and pasting
the code. Works for all DDs. Hopefully you can help me refine for selected
DDs.... (E.g., if I pre-select the DDs I want to change..) THX!

 

[JE McGimpsey]

I answered my first question with a quick test creating a macro and pasting
the code. Works for all DDs. Hopefully you can help me refine for selected
DDs.... (E.g., if I pre-select the DDs I want to change..) THX!

One way:

Public Sub SetSelectedDropDownLinks()
Dim dd As Object
If TypeOf Selection Is DrawingObjects Then
For Each dd In Selection
If TypeOf dd Is DropDown Then
With dd
.LinkedCell = .Parent.Cells( _
.TopLeftCell.Row, "C").Address
End With
End If
Next dd
End If
End Sub

 

[RickGreg]

Public Sub SetSelectedDropDownLinks()
Dim dd As Object
If TypeOf Selection Is DrawingObjects Then
For Each dd In Selection
If TypeOf dd Is DropDown Then
With dd
.LinkedCell = .Parent.Cells( _
.TopLeftCell.Row, "C").Address
End With
End If
Next dd
End If
End Sub

Works perfectly. You have saved untold hours. Thank you! -Rick G