Inserting Drop down on sheet - Array needed

[Les]

Hi all,

I have sheets that i need to put in a drop down, which i have done with
validation, using the code below.

What i am needing is that i have on sheet "Resposibilities" the drop down
information for it in the range("E2:E" & Lastline). I need to get that into
an array, seperated by a "," for the drop down Formula1.

Any help would be much appreciated.... :-0)


Function InsertDropDown()
'
Dim sAllQMT As String

Columns("P").Insert Shift:=xlToRight
Range("P1") = "QMT COMMENTS"
Range("P2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"T3 - Responsibility,T4 -Responsibility,T5-Responsibility,CKD Part"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Selection.Copy
Range("P3" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Function

 

[Jim Thomlinson]

So just to be clear you want your validation list to pull it's values from
your Responsibilities sheet with a list that can grow and shrink? Assuming
that to be the case you probably just want to use a dynamic named range.
Validation lists will allow list from ohter sheets so long as they are a
named range and to have the list be expandable a dynamic named range is
ideal... Check out this link...

http://www.cpearson.com/excel/named.htm

 

[Les]

Thanks Jim
--
Les

So just to be clear you want your validation list to pull it's values from
your Responsibilities sheet with a list that can grow and shrink? Assuming
that to be the case you probably just want to use a dynamic named range.
Validation lists will allow list from ohter sheets so long as they are a
named range and to have the list be expandable a dynamic named range is
ideal... Check out this link...

http://www.cpearson.com/excel/named.htm