Problem with progamatically defined named ranges

[Guest]

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!!

 

[Guest]

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.

 

[Don Guillett Excel MVP]

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"

 

[lmiguel]

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.