Adding named range gives error "method range of object _Global failed "
 

Hi,

Can anybody help me and tell why I get error 1004 "Method Range of object _
Global failed" ?

I try to ensure it always will exist a named range when the workbook is
opened, even if a user somehow deleted one of the hidden named ranges in an
earlier session.

I have this code in ThisWorkbook in Public Sub Workbook_Open() event:

ActiveWorkbook.Names.Add Name:="Sh104Rng",
RefersTo:=Range("C29:G48,C52:G71,C75:G94"), Visible:=False



/Kind regards

Adding named range gives error "method range of object _Global failed "
 

I copied your code and ran it on xl2k. I got no error
message and the code seem to run ok. At first I thought it
might be that you are trying to name 3 ranges at once with
the same name. But my excel took it. 1 name - 3 ranges
highlighted. I just learned something. the only thing i
chaged was visible = true. since I cannot duplicate your
problem, I'm not sure what to tell you. sorry.
-----Original Message-----
Hi,

Can anybody help me and tell why I get error 1004 "Method
Range of object _
Global failed" ?

I try to ensure it always will exist a named range when
the workbook is
opened, even if a user somehow deleted one of the hidden
named ranges in an
earlier session.

I have this code in ThisWorkbook in Public Sub
Workbook_Open() event:

ActiveWorkbook.Names.Add Name:="Sh104Rng",
RefersTo:=Range("C29:G48,C52:G71,C75:G94"), Visible:=False



/Kind regards




.

Adding named range gives error "method range of object _Global failed "
 

- First:
I've find a description of the problem on microsofts site
http://support.microsoft.com/default...NoWebContent=1
- the KnowledgeBase artikel 178510.

It Is caused by a badly qualified raference and that excel doesn't release
it unitl restart excel. " a line of code that calls an Excel object, method,
or property without qualifying it with an Excel object variable ", the
artikel tell me. Imunfortunately I'm not helped by it.

- To reproduce the symptom:
If I copy following code into a new sheet, I get the problem again, so it
had to be into this code and its syntax!:

OBS - In ThisWorkbook module !

Public Sub Workbook_Open()
ActiveWorkbook.Names.Add Name:="Sh104Rng",
RefersTo:=Sheet1.Range("C29:G48,C52:G71,C75:G94"), Visible:=True
End Sub

It worked well one time - sometime even two - just as Microsoft tell us.
Some other time, the problem seem to be reproduced directly when I shoosed
the expression "Name" in Watch Window in VBA interface.

When I move my pointer over "Name:=" in ActiveWorkbook.Names.Add
Name:="Sh104Rng"... in the VBA programming interface in debugging mode, it
show me the filename "Workbook.xls".


Can Anyone help me after all this problem descriptions?

/Kind Regards


"Frank Stone" skrev i meddelandet
...
I copied your code and ran it on xl2k. I got no error
message and the code seem to run ok. At first I thought it
might be that you are trying to name 3 ranges at once with
the same name. But my excel took it. 1 name - 3 ranges
highlighted. I just learned something. the only thing i
chaged was visible = true. since I cannot duplicate your
problem, I'm not sure what to tell you. sorry.
-----Original Message-----
Hi,

Can anybody help me and tell why I get error 1004 "Method
Range of object _
Global failed" ?

I try to ensure it always will exist a named range when
the workbook is
opened, even if a user somehow deleted one of the hidden
named ranges in an
earlier session.

I have this code in ThisWorkbook in Public Sub
Workbook_Open() event:

ActiveWorkbook.Names.Add Name:="Sh104Rng",
RefersTo:=Range("C29:G48,C52:G71,C75:G94"), Visible:=False



/Kind regards




.

Adding named range gives error "method range of object _Global failed "
 

Try the same code in -

Sub Auto_Open() 'in a normal module

regards,
Peter


It Is caused by a badly qualified raference and that
excel doesn't release
it unitl restart excel. " a line of code that calls an
Excel object, method,
or property without qualifying it with an Excel object
variable ",

OBS - In ThisWorkbook module !

Public Sub Workbook_Open()
ActiveWorkbook.Names.Add Name:="Sh104Rng",
RefersTo:=Sheet1.Range("C29:G48,C52:G71,C75:G94") ,
Visible:=True
End Sub

It worked well one time - sometime even two - just as
Microsoft tell us.
Some other time, the problem seem to be reproduced
directly when I shoosed
the expression "Name" in Watch Window in VBA interface.