How to override "loss of functionality" message when saving Excel spreadsheet?

Question

I have some VBA code that saves the spreadsheet under a given filename. Whenever I save it, I get a warning message about "significant loss of functionality", because there's data validation in the sheet (although the validation still works after I've saved the sheet).

I want to suppress this warning, but I don't think I should use

Application.DisplayAlerts=false
mySheet.saveAs myFilename
Application.DisplayAlerts=true

because I don't want to override the warning that tells the user a file with this name already exists, as I want to protect users from accidentally saving over files). How can I specify that I just want to override the "loss of functionality" warning?

EDIT: I saw an answer to a similar question that recommended saving the workbook in a different format. That won't work here because I'm developing in Excel 2007 but trying to accommodate users who have Excel 2003.

Community · Accepted Answer · 2018-05-08 21:48:18Z

2

You can catch the save event and then prompt the user for information. Here's a quick, untested example I threw together for you:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim iAnswer As Integer
    Dim wkb As Workbook
    Dim oSaveAs As FileDialog

    Set wkb = ActiveWorkbook
    Application.DisplayAlerts = False
    If gbUserGenerated Then
        iAnswer = MsgBox("Would you like to save over previous workbook?" _
                         , vbYesNo, "Workbook Already Exists")
        If iAnswer = 6 Then
            wkb.Save
        Else
            Set oSaveAs = Application.FileDialog(msoFileDialogSaveAs)
            oSaveAs.Show
        End If
    Else
        Set oSaveAs = Application.FileDialog(msoFileDialogSaveAs)
        oSaveAs.Show
    End If
    Application.DisplayAlerts = true


End Sub

gbUserGenerated would just be a global variable (boolean) that tells you if you created the workbook or if it was the user (if the user always creates the workbook then you wouldn't really need it).

Put the code in the Microsoft Excel Object folder in the VBE, put it in the "This Workbook" Module.

To learn more about events go here: http://www.cpearson.com/excel/Events.aspx

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Aug 26, 2011 at 19:32

Jon49

4,6264 gold badges41 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sigil Over a year ago

Hmm, having trouble testing this code. I put it in the VBE for a sheet in a blank workbook. I then saved the workbook, then tried to Save As over the book I'd just made. But this didn't trigger the procedure, just gave me the usual Save As dialog. Also, I don't understand what the SaveAsUI and Cancel booleans are for, as they don't show up in the procedure body.

Jon49 Over a year ago

In the Microsoft Excel Object folder in the VBE, put it in the "This Workbook" Module. I'll update my original post to reflect that and put a link to where you can learn more about VBA events. Also, I didn't test the code but it should work. I'll test it after I update the original post.

Jon49 Over a year ago

I tested it and it worked as expected. I took out the global variable and if else statement associated with it first. You might have to change certain things in the code to get it to do exactly what you would like it to do.

Collectives™ on Stack Overflow

How to override "loss of functionality" message when saving Excel spreadsheet?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related