Copying Combo Boxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RickGreg - 28 Aug 2007 16:35 GMT

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

Reply to this Message

JE McGimpsey - 28 Aug 2007 16:54 GMT

> 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
[quoted text clipped - 3 lines]
> 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

Reply to this Message

RickGreg - 28 Aug 2007 17:32 GMT

>> 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
[quoted text clipped - 14 lines]
> 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!

Reply to this Message

RickGreg - 28 Aug 2007 17:37 GMT

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!

Reply to this Message

JE McGimpsey - 28 Aug 2007 17:47 GMT

> 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

Reply to this Message

RickGreg - 28 Aug 2007 17:55 GMT

> Public Sub SetSelectedDropDownLinks()
> Dim dd As Object
[quoted text clipped - 9 lines]
> End If
> End Sub

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

Reply to this Message

Mac Forum / Applications / Excel / August 2007

Copying Combo Boxes