none
Block SaveAs Panel in Excel 2013 RRS feed

  • Question

  • Hi All,

    I have used the following script for several years now, but on porting something across to Excel 2013 it no longer works as desired and the SaveAs Panel opens regardless.

    The script I use is:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        
        Dim strMsgBoxTitle As String
        Dim strMsgBoxBody As String
        Dim vbvMsgBoxStyle As VbMsgBoxStyle
        Dim vbvMsgBoxResult As VbMsgBoxResult
        
        If UserClass = 3 Then
            strMsgBoxTitle = "ProjectCalc"
            strMsgBoxBody = "Save as new version?"
            vbvMsgBoxStyle = vbYesNo + vbQuestion + vbDefaultButton2
            vbvMsgBoxResult = MsgBox(strMsgBoxBody, vbvMsgBoxStyle, strMsgBoxTitle)
            If vbvMsgBoxResult = vbYes Then
                Application.Run "ResetForNewVersion"
            Else
                GoTo SaveProject
            End If
        Else
            GoTo SaveProject
        End If
        Exit Sub
        
    SaveProject:
        ThisWorkbook.Saved = True
        Cancel = True
        Application.Run "SaveProject", 1
    
    End Sub

    Before the MsgBox appears, the SaveAs panel now opens in 2013 as per the screen grab below.  It makes no difference if I move the "Cancel = True" to the top or even pull the SaveAsUI = False command out of the "SaveProject" Sub and put this as line one in this sub either.  The SaveAs Panel still opens.

    This is a file that's opened from a template ".XLTM"

    Once you select any random location to save, the scripts take over and takes the user to the correct directory regardless of what they choose, so they end up wasting time browsing through to a directory only then to find that the system has taken them somewhere else regardless.

    I've searched here for the solution, I've Googled for the solution and everyone is using the solution I already have in place.  Only it no longer works as the panel appears to activate before the BeforeSave function.

    Help please!


    • Edited by Antony White Wednesday, February 18, 2015 5:43 PM Added context
    Wednesday, February 18, 2015 5:40 PM

Answers

  • Joel,

    UserClass is a custom function I wrote to determine who has access rights to various things, in this case, to save a modified template, so a UserClass of 3 gives authoring rights in this case.

    Thank you for looking into it, I stumbled across the solution myself in the end by turning off the BackStage option for save and open.  now all I need to do is work out how to make that change with VBA so that it is disabled on all users without me having to get IT to go around to everyones machine for me.

    • Marked as answer by Antony White Thursday, February 26, 2015 10:44 AM
    Thursday, February 26, 2015 10:44 AM

All replies

  • Does UserClass = 3?

    jdweng

    Thursday, February 19, 2015 12:21 AM
  • Hi Antony,

    I can reproduce this issue in *.xltm file by using the code you post. Based on my understanding, the end user will usually use the ".xlsm" file, and as I tested, this issue did not exist in the ".xlsm" file. So I think you could use ".xlsm" file as a quick workaround.

    Sorry for the inconvenience and thanks for your understanding.

    ​Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, February 19, 2015 7:53 AM
  • Joel,

    UserClass is a custom function I wrote to determine who has access rights to various things, in this case, to save a modified template, so a UserClass of 3 gives authoring rights in this case.

    Thank you for looking into it, I stumbled across the solution myself in the end by turning off the BackStage option for save and open.  now all I need to do is work out how to make that change with VBA so that it is disabled on all users without me having to get IT to go around to everyones machine for me.

    • Marked as answer by Antony White Thursday, February 26, 2015 10:44 AM
    Thursday, February 26, 2015 10:44 AM
  • Okay, it would appear that you cant change the "Don't show the Backstage when opening or saving files" setting via VBA, at least I cant see the commands anywhere.

    Manual set up it is.

    Thursday, February 26, 2015 11:39 AM