Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with progamatically defined named ranges
In Excel 2003, I am defining a named range with the statement"
ThisWorkbook.Names.Add Name:="MyName", _ RefersTo:=Worksheets("X").Range("$A$2:$A$33") This statement execures fine. If I follow this by the code: Set r = ThisWorkbook.Names("MyName").RefersToRange Debug.Print r.Address It prints: $A$2:$A$33 If I use For n = 1 To ThisWorkbook.Names.Count Debug.Print ThisWorkbook.Names(n).Name & " -- " & _ ThisWorkbook.Names(n) Next n it prints: MyName -- =[MyWorkboonName]X!$A$2:$A$33 All this is as I expect. HOWEVER: 1. If I select A2:A33 the name doesn't show in the name window 2. If I use Range(MyRange).Validation.Add Type:=xlValidateList, _ Formula1:="=MyName" I get an error that MyName is not a valid name 3. If in Excel I select Insert/Name/Define, MyName is not is the list of defined names If I define the named range manually by selecting it and typing the name in the name box then all of the above works fine. Apparently the names.add statement is defining the name in some other collection. So, I guess the names.add statement is wrong somehow. How can I do it correctly? Secondly, what is the statement I use actually doing. It is clearly doing something, but the wrong thing. Thanks!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with progamatically defined named ranges
Duh!
It works fine. I failed to mention (because I didn't think about it) that the code was in an addin. I should have used ActibeWorkbook instead of ThisWorkbook. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with progamatically defined named ranges
On Jul 2, 10:27*am, wrote:
In Excel 2003, I am defining a named range with the statement" * *ThisWorkbook.Names.Add Name:="MyName", _ * *RefersTo:=Worksheets("X").Range("$A$2:$A$33") This statement execures fine. * If *I follow this by the code: * *Set r = ThisWorkbook.Names("MyName").RefersToRange * *Debug.Print r.Address It prints: *$A$2:$A$33 If I use * For n = 1 To ThisWorkbook.Names.Count * * * *Debug.Print ThisWorkbook.Names(n).Name & " -- " & _ * * * * * * * * * ThisWorkbook.Names(n) * * * *Next n it prints: MyName -- =[MyWorkboonName]X!$A$2:$A$33 All this is as I expect. HOWEVER: * *1. *If I select A2:A33 the name doesn't show in the name window * *2. *If I use * * * * * Range(MyRange).Validation.Add Type:=xlValidateList, _ * * * * * * * * * * * * * * *Formula1:="=MyName" * * * *I get an error that MyName is not a valid name * *3. *If in Excel I select Insert/Name/Define, MyName is not is the list * * * * * of defined names If I define the named range manually by selecting it and typing the name in the name box then all of the above works fine. *Apparently the names.add statement is defining the name in some other collection. *So, I guess the names.add statement is wrong somehow. How can I do it correctly? Secondly, what is the statement I use actually doing. *It is clearly doing something, but the wrong thing. Thanks!! I tested your macro to name the range just fine. I usually just use Worksheets("X").Range("$A$2:$A$33"),name="MyName" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with progamatically defined named ranges
On Jul 2, 11:01*am, wrote:
Duh! It works fine. *I failed to mention (because I didn't think about it) that the code was in an addin. *I should have used ActibeWorkbook instead of ThisWorkbook. ActiveWorkbook is for the book that is active ThisWorkbook is the book that contains the code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Problem with named ranges | Excel Programming | |||
Reviewing a list of defined named ranges | Excel Programming | |||
VBA Script to Print several named ranges to PDF with cell-defined filenames | Excel Programming | |||
ADO - recordset - closed excel workbook - know sheet name and cell name but no named ranges defined | Excel Programming |