The code below was kindly written for me by Dave Peterson. It fills the
range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5
and offsets the outputs by 1 column. This has proven an extremley useful
tool over the last few months.

I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e.
another tab, and am stuggling. Any ideas? Help much appreciated.


Kind regards,

Neil


Sub COMBOboxes()

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)
myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.ListIndex = 1 '<-- added
End With
Next myCell
End With
End Sub

Hi Neil,

change
Set wks = ActiveSheet by

Set ws = Worksheets("Sheet2")

"Neil Pearce" wrote:

The code below was kindly written for me by Dave Peterson. It fills the
range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5
and offsets the outputs by 1 column. This has proven an extremley useful
tool over the last few months.

I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e.
another tab, and am stuggling. Any ideas? Help much appreciated.


Kind regards,

Neil


Sub COMBOboxes()

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)
myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.ListIndex = 1 '<-- added
End With
Next myCell
End With
End Sub

The dropdowns go on the activesheet, but the list to fill those dropdowns is on
Sheet2???

change this:

myDD.ListFillRange _
= .Parent.Range("z1:z5").Address(external:=True)

to

myDD.ListFillRange _
= worksheets("Sheet2").Range("z1:z5").Address(extern al:=True)

The linked cells still go on the same sheet as the dropdowns, right???

Neil Pearce wrote:

The code below was kindly written for me by Dave Peterson. It fills the
range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5
and offsets the outputs by 1 column. This has proven an extremley useful
tool over the last few months.

I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e.
another tab, and am stuggling. Any ideas? Help much appreciated.

Kind regards,

Neil

Sub COMBOboxes()

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)
myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.ListIndex = 1 '<-- added
End With
Next myCell
End With
End Sub

--

Dave Peterson

myDD.ListFillRange _
=Sheets("Sheet2").Range("z1:z5").Address(external: =True)

Works for me.


Gord Dibben MS Excel MVP

On Mon, 5 Jan 2009 05:57:25 -0800, Neil Pearce
wrote:

The code below was kindly written for me by Dave Peterson. It fills the
range of cells A1:A30 with combo boxes dependant on an list fill fange Z1:Z5
and offsets the outputs by 1 column. This has proven an extremley useful
tool over the last few months.

I wish to modify the code for a list fill range of Z1:Z5 on "Sheet2", i.e.
another tab, and am stuggling. Any ideas? Help much appreciated.


Kind regards,

Neil


Sub COMBOboxes()

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)
myDD.LinkedCell = .Offset(0, 1).Address(external:=True)
myDD.ListIndex = 1 '<-- added
End With
Next myCell
End With
End Sub