5

How do I suppress the dialog that says "Visual Basic macros will be removed if you save the file in this format. Are you sure you want to use this format?" (see picture below).

Essentially, I'm trying to use a macro to save a .xlsm file as an .xslx file?. I've tried all the dialog suppression code I can find and it isn't working.

Here's my code:

Private Sub SaveWithNoMacros()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename:="Clean Workbook With No Macros.xlsx", _  
       FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 End Sub

Here's the dialog:

Excel Dialog

As you might imagine, I need to convert a lot of workbooks and would prefer not to do this manually.

-D.

Community
  • 1
  • 1
D. Woods
  • 3,004
  • 3
  • 29
  • 37
  • Do any of the answers [here](http://stackoverflow.com/q/18899824/1048539) work? I had a very similar situation recently. It may be OS:X handles this differently than windows for displayalerts. – enderland Oct 11 '13 at 23:33
  • @enderland, Good suggestion. Hadn't thought of copying the sheets to a new/clean workbook and then saving. It's a bit of a hack, but I might just implement it. Thanks again. – D. Woods Oct 14 '13 at 14:06

2 Answers2

7

Application.DisplayAlerts = False is the correct statement to disable this dialog. But, as you have experienced, this does not work on Excel 2011. Sadly, there is no way to get around this when you use Excel 2011. Were you using any other version (on a PC) it would have worked just fine.

Netloh
  • 4,338
  • 4
  • 25
  • 38
2

This worked for me

Save as Type: (choose =>) Excel Macro-Enabled Workbook (*.xlsm)

Chris
  • 45
  • 1