Combo Box Code

[Neil Pearce]

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

 

[Eduardo]

Hi Neil,

change
Set wks = ActiveSheet by

Set ws = Worksheets("Sheet2")

 

[Dave Peterson]

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(external:=True)

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

 

[Gord Dibben]

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

Works for me.


Gord Dibben MS Excel MVP