We have a monster of a VBA spreadsheet here that I've posted many times about but something SO SIMPLE it giving me headaches.

The existing sheet opens, pulls in an XLA file and the allows the users to import and manipulate the data. It contains our company log on most of the sheets in the workbook.

We sometimes do these as a private label so the end users has to insert that logo into each of the sheets. Management asked me to create separate sheets for each private label and we just have 2 for now.

I created one and it works fine. I named it the same name as the existing sheet but copied it to a separate folder. I created another one and I get the error:

SO I tried it again. Same error. So, I changed the one private label sheet that works to the 2nd logo. Same error. So I went very simple. I took the master sheet, and simple saved AS into a separate directory with no logo edits. SAME ERROR. If I simply copy the master sheet to different folder with no saving or editing, it works fine but as soon as I edit and save and try to run, bam, get that error on startup.

This is "wonky" as it worked for the first one and now I can't do it at all. I am really beginning to hate this monster of a VBA beast.

These sheets are in Microsoft Excel 97-2003 format and I am using Excel 2013. As mentioned, the first sheet I made the private label on has NO problems.

Here's my solution...I made a copy of the original spreadsheet. Renamed it in explorer. Opened the sheet and changed out the logo's. Saved the sheet and exited. Then, in explorer, I renamed it back to the original name (and it is in its own Directory.) It works now...go figure!

3 Replies

Obviously this is hard to troubleshoot without seeing it. And apologies in advance if you've already considered this, but have you checked to see if Excel 2013 might be holding open some VBA modules from other open/connected Excel files?

If you take a look in the VBA window when working with this problem copy file, can you see multiple projects in the project explorer? Is it possible that the Excel VBA environment is checking for naming conflicts across all open projects?

Copying the master sheet to a new folder won't involve the VBA editor doing anything, so that will be allowed, but have I understood correctly that as soon as you open and save the file - even without any edits - the naming conflct is detected? Can you try renaming the VBA project or module, or just make sure that no conflicting named projects/modules are open in the Project explorer at the time you save?

Obviously this is hard to troubleshoot without seeing it. And apologies in advance if you've already considered this, but have you checked to see if Excel 2013 might be holding open some VBA modules from other open/connected Excel files?

If you take a look in the VBA window when working with this problem copy file, can you see multiple projects in the project explorer? Is it possible that the Excel VBA environment is checking for naming conflicts across all open projects?

Copying the master sheet to a new folder won't involve the VBA editor doing anything, so that will be allowed, but have I understood correctly that as soon as you open and save the file - even without any edits - the naming conflct is detected? Can you try renaming the VBA project or module, or just make sure that no conflicting named projects/modules are open in the Project explorer at the time you save?

Good luck!

Well, first thing I did today was fire up a test box on it with Excel 2003...took the original sheet and saved as in a different location with NO edits. Closed it, reopened in the new location and same error:

Here's my solution...I made a copy of the original spreadsheet. Renamed it in explorer. Opened the sheet and changed out the logo's. Saved the sheet and exited. Then, in explorer, I renamed it back to the original name (and it is in its own Directory.) It works now...go figure!

0

This topic has been locked by an administrator and is no longer open for commenting.