Debug this?

[hke]

This line:

Set wksA = gwbB.Names("aName").RefersToRange.Parent

produces the 1004 Application or object defined error in my program, and I can`t figure out why. The statement resides in an addin and tries to set a reference to a sheet (in another project) with a range referred to by the name aName.

wksA is local to the sub in which the error occurs. wksA is declared as

Dim wksA as Worksheet

Debug.print gwkbB.Name shows me the name of the project is correct and the project certainly has the name aName in it and it refers to a range ok.

Could it be that one needs to set a reference to some special object library to do this?

Any help is much appreciated.

[Leith Ross]

Hello Hke,

The error occurrs because you are attempting to set a Worksheet object equal to a Range object. It like Apples and Oranges. When using Set both objects must be the same or you get the error.

Sincerely,
Leith Ross

[hke]

thanks for your reply
very well. How then, do you set a sheet variable to refer to a sheet in project, gwbkB, knowing the name of a range on that sheet

[Leith Ross]

Hello Hke,

This should work.

WksB = gwbB.Names("aName").RefersToRange.Parent.Name
Set wksA = gwbB.Worksheets(WksB)

Sincerely,
Leith Ross

[hke]

I still get the 1004 error on that first line of code.

debug.print gwbB.Names("aName").RefersToRange.Parent.Name

also makes a 1004

[Leith Ross]

Hello Hke,

Check if WksB contains the Workbook name as well as the Worksheet name by placing a breakpoint in the code next to Set.

Let know,
Leith Ross

[hke]

Again, thanks for your effort Leith

wksB equals Nothing. It is the first line ,

wksB = gwbB.Names("aName").RefersToRange.Parent.Name

, that makes the 1004 happen.

Could a language incompatibility cause this conflict? I suspect this because the names of the sheets in gwbB are displayed as

"Sheet1 (Sheet1name)"

in the project browser. However, if do a Sheets.Add in some other project I get the norwegian

"Ark1(Ark1)"

in the browser.

[Leith Ross]

Hello Hke,

That is certainly a possibility. I didn't realize that this was a multi-lingual setup. Unfortunately, I don't know if or how the code pages could be manipulated to make this work. If you will be using workbooks in both langauges this could be a major obstalce with no easy solutions. Sorry I don't have any other ideas.

Sincerely,
Leith Ross