validation list from worksheet given by ref

[Boba]

Hi;
in my formula, how do i reference a range from
a worksheet given by reference? Say i wonna create
validation list for Workbooks(1).Sheets(1).Cells("A1")
based on the following:
Dim RefWS As Worksheet
Set RefWS=Workbooks("RefBookName").Sheets("RefSheetName")
With Workbooks(1).Sheets(1).Cells("A1").Validation
.Add Type:=xlValidateList _
Formula1:="=RefWS.Cells("A1:A6")"
End With
Thanx.Boba.

 

[Tom Ogilvy]

Formula1:="=" & RefWS.Cells("A1:A6").Address(1,1,xlA1,True)

or if you already know the name,

Formula1:="refSheetName!A1:A6"

 

[Boba]

Formula1:="=" & RefWS.Cells("A1:A6").Address(1,1,xlA1,True)
...

Thank you, Tom;
but the code you suggest returns rte=13
(RefWS is a valid reference)

 

[Boba]

Formula1:="=" & RefWS.Cells("A1:A6").Address(1,1,xlA1,True)
...

strangely enough: with Formula1:="=" &
RefWS.Range("A1:A6").Address(1,1,xlA1,True)
i get rte=1004 (my excel came with ms office10).

 

[Tom Ogilvy]

Well, you have multiple problems I believe. Your sample has several errors
in it. conceptually, it looks like you want to do validation to a list in a
separate workbook. You can't even do validation to a list on a separate
sheet in the same workbook unless you create a named range. As I recall,
you can't do it to a separate workbook. Anyway, this creates the data
validation for a list on another worksheet in the same workbook:

Sub bbb()
Dim RefWS As Worksheet
With Workbooks("RefBookName.xls")
Set RefWS = _
.Worksheets("refSheetname")
.Names.Add _
Name:="List", RefersTo:="=" & _
RefWS.Range("A1:A6").Address(1, 1, xlA1, True)

With .Sheets(1).Range("A1").Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="=List"
End With
End With
End Sub

YOu can play with that and see what you can do.

 

[Boba]

...
conceptually, it looks like you want to do validation to a list in a
separate workbook.

Yes. And i'm sorry for not beeing clear in my op
regarding Workbooks(1) and Workbooks("RefBookName")
- they are different workbooks. Giving the name
to the list has solved the problem.
Thanks for your reply. Boba.