shankar
04-13-2009, 10:08 AM
I am trying to create a drop-down validation for a workbook wkb2 created by a VBA add-in (lets call this wkb1). The validation list in wkb1, but the cells that need to be validated are in wkb2.
I name the range in wkb1 as follows-
ThisWorkbook.Names.Add Name:="list1", RefersToR1C1:="=Sheet2!R2C3:R8C3"
In wkb2, I create a new name as follows-
ActiveWorkbook.Names.Add Name:="list2", RefersToR1C1:="= wkb1!list1"
'ActiveWorkbook refers to wkb2
and for the validation, I use something like-
With .Range("B5").Cells.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=list2"
End With
and it works fine.
My problem is the file name wkb1 is hardcoded. If I want my code to not depend on the name of the add-in, and change the definition of list2 as
ActiveWorkbook.Names.Add Name:="list2", _
RefersToR1C1:="=ThisWorkbook!list1"
it doesn't work, though ThisWorkbook always refers to the add-in. I tried
RefersToR1C1:="=ThisWorkbook.Name!list1", still doesn't work. I also tried assigning another worksheet variable first to ThisWorkBook and use that in the above, but to no avail.
Is there a way of making a drop down list from a named range in the add-in without hard-coding the name of the add-in file? Thanks.
Shankar
I name the range in wkb1 as follows-
ThisWorkbook.Names.Add Name:="list1", RefersToR1C1:="=Sheet2!R2C3:R8C3"
In wkb2, I create a new name as follows-
ActiveWorkbook.Names.Add Name:="list2", RefersToR1C1:="= wkb1!list1"
'ActiveWorkbook refers to wkb2
and for the validation, I use something like-
With .Range("B5").Cells.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=list2"
End With
and it works fine.
My problem is the file name wkb1 is hardcoded. If I want my code to not depend on the name of the add-in, and change the definition of list2 as
ActiveWorkbook.Names.Add Name:="list2", _
RefersToR1C1:="=ThisWorkbook!list1"
it doesn't work, though ThisWorkbook always refers to the add-in. I tried
RefersToR1C1:="=ThisWorkbook.Name!list1", still doesn't work. I also tried assigning another worksheet variable first to ThisWorkBook and use that in the above, but to no avail.
Is there a way of making a drop down list from a named range in the add-in without hard-coding the name of the add-in file? Thanks.
Shankar