+ Reply to Thread
Results 1 to 5 of 5

Excel will not save without deleting code

  1. #1
    Registered User
    Join Date
    08-19-2012
    Location
    NZ
    MS-Off Ver
    Excel 2010
    Posts
    5

    Unhappy Excel will not save without deleting code

    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:

    Please Login or Register  to view this content.
    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

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Excel will not save without deleting code

    Is the variable FileFormatValue not meant to be used when you are saving the file?
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-19-2012
    Location
    NZ
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel will not save without deleting code

    Thanks Norie, to be honest I got the code off of a google search; I am quite new to VBA - this is only my second VBA project.

    That being said what you are suggesting sounds right; do I simply change

    Please Login or Register  to view this content.
    To this:

    Please Login or Register  to view this content.
    ?

    I imagine I have to define the FileFormatValue variable somewhere in the code, any idea how a total noob would do that? :/

    All this being said, how would it would work fine in one form and not another when nothing changed?

    Thanks again for the help!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Excel will not save without deleting code

    The variable is declared here.
    Please Login or Register  to view this content.
    It should get it's value here.
    Please Login or Register  to view this content.
    So, I don't think there is anything you need to do.

  5. #5
    Registered User
    Join Date
    08-19-2012
    Location
    NZ
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel will not save without deleting code

    That did the trick, it seems to work now! I still wonder how it ever worked without using that variable... mind boggling really.

    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1