Currently, in Excel 2010, when I try to save a workbook that I have created as a .xlsx file but added a VBA function or subroutine to, Excel gives a prompt asking (in a round-about way) if I want to change it to a .xlsm file. This wouldn't be a problem for me if the default answer to this question was "yes I would prefer to not have all the programming work I did while working on this file deleted because I saved", but you need to actively click on or tab over to the "No" button or else it saves as a .xlsx file and you lose all your modules.

This is one of the only situations I have ran into where the default option is to delete data rather than save it. I know the fact that I have lost some of this work is mostly my fault and I should be more careful, but I would really like to know if I can disable this message or change the default somehow.

  • I assume that you mean you have it saved as an XLSX file. XLS files can contain VBA and give no warnings on save. – Engineer Toast Mar 11 '15 at 13:24
  • @Engineer Toast Your are correct, I was talking about .xlsx files. But I did not know this about .xls files, changing to .xls files would be an interesting solution to this problem. Thanks! – nateAtwork Mar 11 '15 at 15:39
  • XLS is the format from 1997 - 2003 and, thus, lacks certain features found in 2007 and later. Depending on how fancy your files are, it may or may not work. There are also security concerns and other users may get warning messages that the XLS file may be unsafe as it contains macros. Just a heads up. – Engineer Toast Mar 11 '15 at 15:55

You can but it's messy and dangerous.

  1. Allow VBA to read / write VBA (File > Options > Trust Center > Trust Center Settings > Macro Settings > Trust access to the VBA project object model)
  2. Create a personal macro workbook (if you don't already have one)
  3. In the ThisWorkbook object for your personal macro workbook, use the Workbook_Open event to map Ctrl+S to some other macro. (It'll be something like Application.OnKey "^s", "saveAsMacroIfMacrosExist")
  4. Use the Workbook_BeforeClose event to turn if off again (Application.OnKey "^s")
  5. Go write that subroutine - not a function - in a module in the personal macro workbook. (This is left as an exercise for the reader but I found a snippet here that I did not test but is supposed to test whether or not a workbook has macros in it.)
  6. Use Ctrl+S to save your files instead of the toolbar or File > Save.

Key Point: This requires that you permanently allow access to the VBA project object model which is, in general, a bad idea.

| improve this answer | |
  • I accepted your answer because it is the answers the question I actually asked, although I think just switch to default saving as macro enabled would be a better method for me. Thanks again! – nateAtwork Mar 11 '15 at 15:40
  • Although I just noticed this would not work for pre-existing files or files created by other users, so I may yet go back to your solution. – nateAtwork Mar 11 '15 at 15:50
  • @nateAtwork I don't actually recommend using this method unless you plan to turn it on just when you're working with these files. Allowing all macros to access your VBA object opens you up to malicious code. You'll probably be fine but I like to play it safe. – Engineer Toast Mar 11 '15 at 15:56

You can not change this on a workbook level.

However you can allow Excel to always enable all macros by default.

  1. Click File > Options.
  2. Click Trust Center and Trust Center Settings.
  3. Click Macro Settings and Enable all macros

It's in the documentation

| improve this answer | |
  • I already had these settings when I was getting the cautionary message with a default answer that would delete my macros. – nateAtwork Mar 11 '15 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.