0

I have an excel spreadsheet named "data_covnertot_0.25.xlsm" that has a tonne of VBA in it. When opening, after the user clicks "Enables Macros" a couple of warnings appear. These errors occur before any Macros have been run.

There are a few similar queries on Stack Overflow but they either haven't been answered or are regarding add-ons of which I am not using any.

The spreadsheet is saved on ProjectWise however, the error messages occurs regardless of where it is saved.

The error message is:

"Sorry, we couldn't find '...........Copy of data_covnertor_0.18.xlsm'. Is it possible it was moved, renamed or deleted?"

followed a second error message:

"Microsoft Excel cannot access the file '...........Copy of data_covnertor_0.18.xlsm'. There are several possible reasons"
    The file name or path does not exist
    The file is being used by another program
    The workbook you are trying to save has the same name as as a currently open workbook

The spreadsheet works fine after clicking ok to both messages, bit as this will be used by a lot of people I want it to be clean and neat.

The file link in the messages is to a share point site, which is where the spreadsheet was saved before being moved to ProjectWise.

I've had a look through all the VBA, sheets etc, carrying out searches etc, and I can't find any reference to the copy of the old version of this spreadsheet.

Can anyone advise where I should be looking

Richard
  • 439
  • 3
  • 25
  • Look in range names - your sheet may have a range name defined which is referring to a cell reference in another sheet, not sure where it is on the ribon but if you alt+I then N and then D it brings up the name range manager and you can see links there – JohnnieL Feb 04 '21 at 12:37
  • 1
    Those messages are not Windows message, they are produced by the Workbook VBA code. So, open the VBE, press `Ctrl + F`, type in 'Find What window: "Sorry, we couldn't find" (but exactly what the message displays), choose 'Curent Project' in the 'Search radio buttons group` and press 'Enter'. Then analize your code and see what triggers such an error. If there are more such occurrences, insert a new line: `Stop` before to each other, save the workbook and open it again. Proceed the same with the second one and see where is the problem... Solve it, or comment the line if not important... – FaneDuru Feb 04 '21 at 12:50
  • I'm afraid neither of the above has worked. In Ranges (Formulas > Name manager) there were a lot of ranges that seemed to serve no purpose and were named "Externtal_1" for example. Deleting them all didn't fix it. It's not a VBA message. I even deleted every line of VBA and it still does it. Must be something else it seems. – Richard Feb 04 '21 at 14:25
  • Same result saving it as an .xlsx – Richard Feb 04 '21 at 14:37
  • Found it! By systematically deleting sheets, I found the offending sheet There were some buttons which were directing to some macros in an old workbook. Unsure what's a smart way of finding this, but easy fix now I know what it is. – Richard Feb 04 '21 at 14:48

1 Answers1

0

In my case the cause of the error was a button (a form control button) that was referencing a Macro in a different workbook.

I found the offending button by symptomatically deleting sheets and saving copies of the workbook until the error disappeared.

Richard
  • 439
  • 3
  • 25