|
Bad External Link
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 |
Bad External Link - NO ANSWERS?
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 |
Bad External Link - NO ANSWERS?
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 |
Bad External Link - ANSWERS?
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 |
Bad External Link - It's baaack :-(
Dave,
1. Thanks. I have both of those utilities and they are excellent. 2. I don't recall using View|Custom, but I may have at one point. In any case I deleted those 2 names. 3. The problem is back after one day of successful use and no changes other than saving it. I went through everything again and cannot find any suspicious links. I used Name Manager and checked all it's various (very excellent) sort options to no avail. I can only assume it's something in my code now causing this problem, but that makes no sense either. 4. Still, it's very weird that: a) it only fails if you quit Excel entirely. Closing the workbook causes no problems, and, B) it does not fail using XP. TH On 4/19/04 19:27, in article , "Dave Peterson" wrote: Any chance you used View|Custom Views? They make funny names with WVU in them. And my two favorite utilities for working with names and links a Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager. http://www.jkp-ads.com/Download.htm And Bill Manville's FindLink program to help find those pesky links: http://www.bmsltd.ie/MVP/Default.htm Together, it makes things lots, lots, (and one more!) lots, simpler. TH wrote: 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 |
Bad External Link - It's baaack :-(
I don't think I've ever seen that error.
Any chance it's not your workbook that's causing the trouble? Are there any workbooks that are open at the same time. I'd check the project explorer (ctrl-R) inside the VBE to see if there's something else open that may cause the trouble. Maybe you could close all those other workbooks/addins and see what happens then. TH wrote: Dave, 1. Thanks. I have both of those utilities and they are excellent. 2. I don't recall using View|Custom, but I may have at one point. In any case I deleted those 2 names. 3. The problem is back after one day of successful use and no changes other than saving it. I went through everything again and cannot find any suspicious links. I used Name Manager and checked all it's various (very excellent) sort options to no avail. I can only assume it's something in my code now causing this problem, but that makes no sense either. 4. Still, it's very weird that: a) it only fails if you quit Excel entirely. Closing the workbook causes no problems, and, B) it does not fail using XP. TH On 4/19/04 19:27, in article , "Dave Peterson" wrote: Any chance you used View|Custom Views? They make funny names with WVU in them. And my two favorite utilities for working with names and links a Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager. http://www.jkp-ads.com/Download.htm And Bill Manville's FindLink program to help find those pesky links: http://www.bmsltd.ie/MVP/Default.htm Together, it makes things lots, lots, (and one more!) lots, simpler. TH wrote: 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 -- Dave Peterson |
Bad External Link - It's baaack :-(
Dave,
Checked all that. No other workbooks open. Tried removing my personal macro workbook. No good. Tried it on another Win2K Excel2K system. Same results. Ok on XP system. Isn't a problem on different workbooks. So, MAYBE this is the problem. I noticed that a shape was selected on nearly every worksheet that had a shape. Additionally, a macro assigned to a shape was somehow unassigned. I went through each sheet and made sure no shapes or charts were selected and Quit. The "Bad External Link" error DID NOT APPEAR :-) I did not write any code to select all these shapes, but will look anyway. I don't understand why there were selected. If anyone knows how to select the first shape on all sheets, let me know. We'll see tomorrow what happens when this workbook runs (it's set to open via Windows Scheduler so I can get email reports of my trading systems while out). Thanks for the ideas. TH On 4/20/04 19:38, in article , "Dave Peterson" wrote: I don't think I've ever seen that error. Any chance it's not your workbook that's causing the trouble? Are there any workbooks that are open at the same time. I'd check the project explorer (ctrl-R) inside the VBE to see if there's something else open that may cause the trouble. Maybe you could close all those other workbooks/addins and see what happens then. TH wrote: Dave, 1. Thanks. I have both of those utilities and they are excellent. 2. I don't recall using View|Custom, but I may have at one point. In any case I deleted those 2 names. 3. The problem is back after one day of successful use and no changes other than saving it. I went through everything again and cannot find any suspicious links. I used Name Manager and checked all it's various (very excellent) sort options to no avail. I can only assume it's something in my code now causing this problem, but that makes no sense either. 4. Still, it's very weird that: a) it only fails if you quit Excel entirely. Closing the workbook causes no problems, and, B) it does not fail using XP. TH On 4/19/04 19:27, in article , "Dave Peterson" wrote: Any chance you used View|Custom Views? They make funny names with WVU in them. And my two favorite utilities for working with names and links a Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager. http://www.jkp-ads.com/Download.htm And Bill Manville's FindLink program to help find those pesky links: http://www.bmsltd.ie/MVP/Default.htm Together, it makes things lots, lots, (and one more!) lots, simpler. TH wrote: 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 |
Bad External Link - It's baaack :-(
I still don't have a guess, but you can select the first shape with something
like: Option Explicit Sub testme01() Dim wks As Worksheet For Each wks In Worksheets If wks.Shapes.Count 0 Then wks.Shapes(1).Select End If Next wks End Sub (No hidden sheets, right?) TH wrote: Dave, Checked all that. No other workbooks open. Tried removing my personal macro workbook. No good. Tried it on another Win2K Excel2K system. Same results. Ok on XP system. Isn't a problem on different workbooks. So, MAYBE this is the problem. I noticed that a shape was selected on nearly every worksheet that had a shape. Additionally, a macro assigned to a shape was somehow unassigned. I went through each sheet and made sure no shapes or charts were selected and Quit. The "Bad External Link" error DID NOT APPEAR :-) I did not write any code to select all these shapes, but will look anyway. I don't understand why there were selected. If anyone knows how to select the first shape on all sheets, let me know. We'll see tomorrow what happens when this workbook runs (it's set to open via Windows Scheduler so I can get email reports of my trading systems while out). Thanks for the ideas. TH On 4/20/04 19:38, in article , "Dave Peterson" wrote: I don't think I've ever seen that error. Any chance it's not your workbook that's causing the trouble? Are there any workbooks that are open at the same time. I'd check the project explorer (ctrl-R) inside the VBE to see if there's something else open that may cause the trouble. Maybe you could close all those other workbooks/addins and see what happens then. TH wrote: Dave, 1. Thanks. I have both of those utilities and they are excellent. 2. I don't recall using View|Custom, but I may have at one point. In any case I deleted those 2 names. 3. The problem is back after one day of successful use and no changes other than saving it. I went through everything again and cannot find any suspicious links. I used Name Manager and checked all it's various (very excellent) sort options to no avail. I can only assume it's something in my code now causing this problem, but that makes no sense either. 4. Still, it's very weird that: a) it only fails if you quit Excel entirely. Closing the workbook causes no problems, and, B) it does not fail using XP. TH On 4/19/04 19:27, in article , "Dave Peterson" wrote: Any chance you used View|Custom Views? They make funny names with WVU in them. And my two favorite utilities for working with names and links a Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager. http://www.jkp-ads.com/Download.htm And Bill Manville's FindLink program to help find those pesky links: http://www.bmsltd.ie/MVP/Default.htm Together, it makes things lots, lots, (and one more!) lots, simpler. TH wrote: 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 -- Dave Peterson |
| All times are GMT +1. The time now is 11:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com