Error Message upon opening...

[sanantonio]

Hi All,

Recently built a macro enabled tool, thanks to the efforts of forum member NDNovicehelp, but now I get this error message when I open the document:

There isn't a single formula in the spreadsheet?

I tried running the error checker but just get this back:

Anyway to overcome this? Bit embarrassing having a tool I'm about to share with other teams that has a stray error message every single time you open the document...

Any help greatly appreciated!

 

[NdNoviceHlp]

Hi again sanantonio. Puzzling error. Is this the original file or the copied to file that you're referring to? Maybe close all of your workbooks and check and see if your task manager still has an excel process running. If it does end it. If U are brave, U can also trial making sure that some file isn't in an error state. Close all workbooks and open the registry editor app (type regedit in the search box). Follow this path...
HKEY_CURRENT_USER\Software\Microsoft\Office" & Application.Version & "\Excel\
See if Resiliency is listed. If it is, select it. If there are any numbers underneath it, right click on them and delete. Then select startupitems. If anything is listed in the box to the right of the menu, right click it and then delete it. I have done this without any harmful outcomes but I'm not certain what will happen for U. Maybe just check and see if there are any resiliency items listed as I have no idea whether this has anything to do with your troubles. The only other thought that I have is that perhaps copy/pasting only visible filtered cells as values has something to do with your error? Perhaps others have had some experience with this error and will be able to offer further assistance. Good luck. Dave

 

[johnnyL]

@sanantonio When you get the error message and click the ok button, what do you see at the bottom left corner of the sheet? Screen pic it if need be and post here.

 

[johnnyL]

Here is a hint: If you see something similar to the following, that is confirming the circular reference(s). If it doesn't show an address, click the tabs of the rest of the sheets until you go through them all. The sheet(s) with circular reference formulas will show the address(es) in each sheet that are causing the problem(s).

 

[sanantonio]

Thanks for all the replies guys. @NdNoviceHlp sounds a bit beyond me that! I'll give it a go if I exhaust the alternatives though!

Slight lie there is 2 lines of formulas.

@johnnyL I'd never noticed that bit before. So it says:

So went and checked V4 and deleted it too see what it said. Needless to say it was the entire string of formulas in V and then U. These are the location of this formula:

V4>V20 has this formula, referencing Q4>Q20 which is just a static list of our sites. D24 contains a cell where you can input a date override, which if blank inputs today + 1.
U4>U20 is identical to the above, just B16 instead of B06 in the title (These aren't cell references, just part of the naming convention).

I personally don't see anything circular in this?

 

[sanantonio]

Semi-solved this lone star. Basically deleted all the formulas, then wrote them again. Cleared the error. Very odd!

 

[johnnyL]

Glad to hear that you got it sorted out @sanantonio.