I am getting this message ONLY when I QUIT Excel (2000) with this 1 workbook
open: "Your formula contains an invalid external reference to a worksheet."
If I simply close the workbook (without quitting Excel) the error does not
appear.

I have Googled the Excel newsgroups and tried these solutions to no avail.

1. FINDLNK. This shows no external links.
2. Unmerge every cell in the workbook and rerun FINDLNK. Nothing.
3. Set Application.DisplayAlerts = False in the workbook_beforeclose event
hoping to just by pass the message. No good.
4. Searched for renamed sheets. I renamed a sheet from '7' to '8' about the
time this started happening. No links found for '7'

Now for the weird one:
5. I tried setting breakpoints in the workbook_beforeclose to find the line
of code that was failing. It DOES NOT FAIL with a breakpoint set!

This behavior appeared in this workbook about 5 revisions ago (out of 100+).

Last note: this does NOT happen on a machine running WINXP/EXCELXP. I am
running Win2K SP-4 and Excel2000 SP-3.

I really need this message to go away in order to automate this program
while I'm gone. It's my stock trading program. Any ideas will be greatly
appreciated.

Terry

So, no one knows the answer to my question???
This is not the typical Bad Link problem. Please read and HELP!

On 4/13/04 18:29, in article , "TH"
wrote:

I am getting this message ONLY when I QUIT Excel (2000) with this 1 workbook
open: "Your formula contains an invalid external reference to a worksheet."
If I simply close the workbook (without quitting Excel) the error does not
appear.

I have Googled the Excel newsgroups and tried these solutions to no avail.

1. FINDLNK. This shows no external links.
2. Unmerge every cell in the workbook and rerun FINDLNK. Nothing.
3. Set Application.DisplayAlerts = False in the workbook_beforeclose event
hoping to just by pass the message. No good.
4. Searched for renamed sheets. I renamed a sheet from '7' to '8' about the
time this started happening. No links found for '7'

Now for the weird one:
5. I tried setting breakpoints in the workbook_beforeclose to find the line
of code that was failing. It DOES NOT FAIL with a breakpoint set!

This behavior appeared in this workbook about 5 revisions ago (out of 100+).

Last note: this does NOT happen on a machine running WINXP/EXCELXP. I am
running Win2K SP-4 and Excel2000 SP-3.

I really need this message to go away in order to automate this program
while I'm gone. It's my stock trading program. Any ideas will be greatly
appreciated.

Terry

Don't bother to find out why this works.
Just open a clean template and copy over your data.
Close the other workbook and Save as the new one deleting the old one.

Otherwise if you must know, check the Names you may have a broken link.
Check your Links under the View menu item and click open.
If that don't work cause XL can't find the other file, pick Change Source and point to this open file.

Here is a Macro for you to see all your names as some may be hidden, or you may have several with the same name on different sheets.

Sub ListAllNames3()
Dim n As Name, x As Long
With ActiveCell
For Each n In ActiveWorkbook.Names
n.Visible = True
.Offset(x, 0).Value = n.Name
.Offset(x, 1).Value = " " & n.RefersTo
x = x + 1
Next n
End With
End Sub


You can also check on all your formulas by using the special cells feature F5 Special Formulas Errors OK.
Now all formula with errors are selected for the sheet you are on.


Regards Robert

"TH" wrote in message ...
So, no one knows the answer to my question???
This is not the typical Bad Link problem. Please read and HELP!

On 4/13/04 18:29, in article , "TH"
wrote:

I am getting this message ONLY when I QUIT Excel (2000) with this 1 workbook
open: "Your formula contains an invalid external reference to a worksheet."
If I simply close the workbook (without quitting Excel) the error does not
appear.

I have Googled the Excel newsgroups and tried these solutions to no avail.

1. FINDLNK. This shows no external links.
2. Unmerge every cell in the workbook and rerun FINDLNK. Nothing.
3. Set Application.DisplayAlerts = False in the workbook_beforeclose event
hoping to just by pass the message. No good.
4. Searched for renamed sheets. I renamed a sheet from '7' to '8' about the
time this started happening. No links found for '7'

Now for the weird one:
5. I tried setting breakpoints in the workbook_beforeclose to find the line
of code that was failing. It DOES NOT FAIL with a breakpoint set!

This behavior appeared in this workbook about 5 revisions ago (out of 100+).

Last note: this does NOT happen on a machine running WINXP/EXCELXP. I am
running Win2K SP-4 and Excel2000 SP-3.

I really need this message to go away in order to automate this program
while I'm gone. It's my stock trading program. Any ideas will be greatly
appreciated.

Terry




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 09/04/2004

Robert,

Thanks a bundle. I will need your detailed suggestions as this workbook has
300 names, 20 sheets, 5000 rows per sheet and several thousand lines of VBA.

I have already searched the names with Name Manager to no avail, but I will
try all your suggestions.

ONE QUESTION: Did you mean check my links under the EDIT / LINKS... Menu? I
don't see LINKS in the VIEW menu. By the way, my LINKS... Is greyed out
because there aren't any.

Thanks again.

TH

On 4/17/04 6:40, in article , "Robert
McCurdy" wrote:

Don't bother to find out why this works.
Just open a clean template and copy over your data.
Close the other workbook and Save as the new one deleting the old one.

Otherwise if you must know, check the Names you may have a broken link.
Check your Links under the View menu item and click open.
If that don't work cause XL can't find the other file, pick Change Source and
point to this open file.

Here is a Macro for you to see all your names as some may be hidden, or you
may have several with the same name on different sheets.

Sub ListAllNames3()
Dim n As Name, x As Long
With ActiveCell
For Each n In ActiveWorkbook.Names
n.Visible = True
.Offset(x, 0).Value = n.Name
.Offset(x, 1).Value = " " & n.RefersTo
x = x + 1
Next n
End With
End Sub


You can also check on all your formulas by using the special cells feature F5
Special Formulas Errors OK.
Now all formula with errors are selected for the sheet you are on.


Regards Robert

"TH" wrote in message
...
So, no one knows the answer to my question???
This is not the typical Bad Link problem. Please read and HELP!

On 4/13/04 18:29, in article , "TH"
wrote:

I am getting this message ONLY when I QUIT Excel (2000) with this 1 workbook
open: "Your formula contains an invalid external reference to a worksheet."
If I simply close the workbook (without quitting Excel) the error does not
appear.

I have Googled the Excel newsgroups and tried these solutions to no avail.

1. FINDLNK. This shows no external links.
2. Unmerge every cell in the workbook and rerun FINDLNK. Nothing.
3. Set Application.DisplayAlerts = False in the workbook_beforeclose event
hoping to just by pass the message. No good.
4. Searched for renamed sheets. I renamed a sheet from '7' to '8' about the
time this started happening. No links found for '7'

Now for the weird one:
5. I tried setting breakpoints in the workbook_beforeclose to find the line
of code that was failing. It DOES NOT FAIL with a breakpoint set!

This behavior appeared in this workbook about 5 revisions ago (out of 100+).

Last note: this does NOT happen on a machine running WINXP/EXCELXP. I am
running Win2K SP-4 and Excel2000 SP-3.

I really need this message to go away in order to automate this program
while I'm gone. It's my stock trading program. Any ideas will be greatly
appreciated.

Terry




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 09/04/2004

FIXED...with one more QUESTION.

Just so others can see the solution.

Using Robert's code below (which I believe is also an option on FindLnk) I
discovered 7 Named cells that somehow had duplicate, invalid pointers to a
wrong sheet. All variables existed twice, once pointing to the correct sheet
and again pointing to the incorrect sheet. If you did a GOTO on the name
pointing to the incorrect sheet, it would actually take you to the correct
worksheet. So, deleting these AND deleting two PrintArea names fixed the
error message.

There are still 2 very strange names I did not put there. They refer to the
PrintArea names (in duplicate to the real PrintNames I deleted) with very
long HEX names. Looks a bit like a registry code?? Anyone know what these
are?
(Note: I tried deleted just these names and that did NOT fix the problem.)

'About Rivelle'!Z_8818E7CE_E982_4E9C_992C_F93C4FDBEBE_.wv u.PrintArea
'EMU-PUMA Chart'!Z)8818E7CE_E982_4E9C_992C_F93C4FD4BEBE_.wvu .Rows

TH

On 4/18/04 20:21, in article , "TH"
wrote:

Robert,

Thanks a bundle. I will need your detailed suggestions as this workbook has
300 names, 20 sheets, 5000 rows per sheet and several thousand lines of VBA.

I have already searched the names with Name Manager to no avail, but I will
try all your suggestions.

ONE QUESTION: Did you mean check my links under the EDIT / LINKS... Menu? I
don't see LINKS in the VIEW menu. By the way, my LINKS... Is greyed out
because there aren't any.

Thanks again.

TH

On 4/17/04 6:40, in article , "Robert
McCurdy" wrote:

Don't bother to find out why this works.
Just open a clean template and copy over your data.
Close the other workbook and Save as the new one deleting the old one.

Otherwise if you must know, check the Names you may have a broken link.
Check your Links under the View menu item and click open.
If that don't work cause XL can't find the other file, pick Change Source and
point to this open file.

Here is a Macro for you to see all your names as some may be hidden, or you
may have several with the same name on different sheets.

Sub ListAllNames3()
Dim n As Name, x As Long
With ActiveCell
For Each n In ActiveWorkbook.Names
n.Visible = True
.Offset(x, 0).Value = n.Name
.Offset(x, 1).Value = " " & n.RefersTo
x = x + 1
Next n
End With
End Sub


You can also check on all your formulas by using the special cells feature F5
Special Formulas Errors OK.
Now all formula with errors are selected for the sheet you are on.


Regards Robert

"TH" wrote in message
...
So, no one knows the answer to my question???
This is not the typical Bad Link problem. Please read and HELP!

On 4/13/04 18:29, in article , "TH"
wrote:

I am getting this message ONLY when I QUIT Excel (2000) with this 1
workbook
open: "Your formula contains an invalid external reference to a worksheet."
If I simply close the workbook (without quitting Excel) the error does not
appear.

I have Googled the Excel newsgroups and tried these solutions to no avail.

1. FINDLNK. This shows no external links.
2. Unmerge every cell in the workbook and rerun FINDLNK. Nothing.
3. Set Application.DisplayAlerts = False in the workbook_beforeclose event
hoping to just by pass the message. No good.
4. Searched for renamed sheets. I renamed a sheet from '7' to '8' about the
time this started happening. No links found for '7'

Now for the weird one:
5. I tried setting breakpoints in the workbook_beforeclose to find the line
of code that was failing. It DOES NOT FAIL with a breakpoint set!

This behavior appeared in this workbook about 5 revisions ago (out of
100+).

Last note: this does NOT happen on a machine running WINXP/EXCELXP. I am
running Win2K SP-4 and Excel2000 SP-3.

I really need this message to go away in order to automate this program
while I'm gone. It's my stock trading program. Any ideas will be greatly
appreciated.

Terry




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 09/04/2004