Copying Combo Boxes?!

[Neil Pearce]

Dear all,

Using the forms toolbar I have created a combo box which is located in cell
A1: input range: $Z$1:$Z$5; cell link B1.

If I copy the cell and paste into B1 the combo box is copied. However the
cell link remains as B1?!

Is there a way I can copy the cell and combo box so that the cell link moves
to, i.e. becomes B1?

I need to create 30 cells with combo boxes with the same input range but
individual cell links.


Thanks in advance.


Kind regards,

Neil

 

[Dave Peterson]

I would add the dropdowns via a macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myDD As DropDown
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.DropDowns.Delete 'nice for testing!
Set myRng = .Range("a1:A30")
For Each myCell In myRng.Cells
With myCell
Set myDD = .Parent.DropDowns.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)

myDD.ListFillRange _
= .Parent.Range("z1:z5").Address(external:=True)
myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
End With
Next myCell
End With
End Sub

ps.
I would move that listfillrange to a different worksheet (hidden). It becomes a
pain when you insert/delete rows or columns to remember to make sure Z1:Z5
didn't get broken.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Neil Pearce]

Dave - that's fantastic! You're a star, thank-you.

A final follow up query.

Is there a way to set the combo boes to default their link cells to Z1 when
created?


Very much appreciated indeed.

Kind regards,

Neil

 

[Neil Pearce]

Sorry that should have read...

Is there a way to set the combo boxes to default their link cells output to
the content of Z1 when created?

This would enable me to have a blank cell in Z1 that can be the default
option and also mean that running the macro will reset the workbook.


Thanks again.

 

[Dave Peterson]

The linked cell shows an index into that listrange.

If you want to show the value of Z1 in the dropdown, you could do it in a couple
of ways.

I'd add a line:

myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.ListIndex = 1 '<-- added

 

[Neil Pearce]

Thank-you very much.

One smiling Neil. )

The linked cell shows an index into that listrange.

If you want to show the value of Z1 in the dropdown, you could do it in a couple
of ways.

I'd add a line:

myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.ListIndex = 1 '<-- added