I used to have a problem where users of my VBA-coded form (.xltm) would save filled-in forms as regular (VB-free) workbooks, so I was able to trawl the web and I found a bit of coding that seemed to do the trick perfectly:
I put this code in the 'This Workbook' section, and for a couple of months it worked great. When you try to save a filled-in form, the 'Save As' dialogue opens up, and the only option available is to use a .xlsm file. Many forms were filled in this way, and they still work fine when you open them up. However, I now have a bug that surfaced seemingly without any changes being made to the template!
Now, when I open up the template to fill in a new form I cannot save what has been entered. The Save As dialogue box opens up, but when I click Save I get an error message:
The following features cannot be saved in macro-free workbooks:
VB project
To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes.
If I click No, I go back to the Save As dialogue box, where there is only one option (as enforced by the BeforeSave code above): Excel Macro Enabled Workbook (*.xlsm). Again, the same error appears when you click Save. If I click Yes, no file is saved at all. The only workaround to this that I have found is to delete the entire private sub I pasted in above; after doing this saving works as per standard excel parameters. Put the code back and it doesn't work. I opened up some previously filled in forms (which were created from the same template, yet still work as they should), and the BeforeSave code is exactly the same. I tried removing the BeforeSave code from a new form, then coping and pasting the same BeforeSave code (that works fine) from a previously filled-in form, and I still come up with the same error.
What could cause an error/bug such as this? Anyone have alternative solutions for ensuring the filled-in forms are only saved with their macros? Is this a bug in Excel itself?? Your comments and assistance are greatly appreciated
Bookmarks