Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list from worksheet given by ref
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("RefSheetNam e") With Workbooks(1).Sheets(1).Cells("A1").Validation .Add Type:=xlValidateList _ Formula1:="=RefWS.Cells("A1:A6")" End With Thanx.Boba. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list from worksheet given by ref
Formula1:="=" & RefWS.Cells("A1:A6").Address(1,1,xlA1,True)
or if you already know the name, Formula1:="refSheetName!A1:A6" -- Regards, Tom Ogilvy "Boba" wrote: 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("RefSheetNam e") With Workbooks(1).Sheets(1).Cells("A1").Validation .Add Type:=xlValidateList _ Formula1:="=RefWS.Cells("A1:A6")" End With Thanx.Boba. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list from worksheet given by ref
"Tom Ogilvy" wrote in message
... 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list from worksheet given by ref
"Tom Ogilvy" wrote in message
... 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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list from worksheet given by ref
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. -- Regards, Tom Ogilvy "Boba" wrote: "Tom Ogilvy" wrote in message ... 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list from worksheet given by ref
"Tom Ogilvy" wrote in message
... ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
validation list on different worksheet | New Users to Excel | |||
drop-down list validation won't allow a different worksheet | Excel Worksheet Functions | |||
Data validation list from another worksheet? | Excel Discussion (Misc queries) | |||
Validation from list in different worksheet/workbook | Excel Programming | |||
Validation - List - Separate Worksheet | Excel Worksheet Functions |