none
Save WorkBook RRS feed

  • Question

  • I would like to include some code in the Before Save event which controls what the workbook is called.

    For example, if the workbook is called TEST1.XLSM I do not want the user to be able to change the name of the Workbook when saving it - with one exception.

    If the user attempts to change the name then they should be asked if the want to create an offline version and if they answer "yes" we can change the name toe TEST1OFFLINE.XLSM otherwise it defaults to TEST1.XLSM

    I have tried this and I am ok if the answer is no, but I can't get it to work if the answer is yes.

    any suggestions welcome....

    many thanks ....Peter

    Wednesday, May 13, 2015 11:09 AM

Answers

  • Hello again,

    I would sugest you make your custom "save as" dialoge.

    eg.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
       
        Dim dl As FileDialog
       
       
        
        Set dl = Application.FileDialog(msoFileDialogSaveAs)
       
        If dl.Show = True Then
       
            If dl.SelectedItems(1) <> ThisWorkbook.Path Then
                If MsgBox("really save?", vbOKCancel) = vbOK Then
                    Application.EnableEvents = False
                    Call ThisWorkbook.SaveAs(dl.SelectedItems(1))
                    Application.EnableEvents = True
                End If
            Else
                Application.EnableEvents = False
                Call ThisWorkbook.Save
                Application.EnableEvents = True
            End If
        End If
       
        Cancel = True
       
    End Sub

    Hope it helps.

    • Proposed as answer by Wouter Defour Monday, May 18, 2015 2:28 PM
    • Marked as answer by py1 Monday, May 18, 2015 2:42 PM
    Wednesday, May 13, 2015 1:53 PM

All replies

  • Hello,

    are you looking for something like this?

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

    If MsgBox("save?", vbOKCancel) = vbOK Then
            Application.EnableEvents = False
            ThisWorkbook.SaveAs "c:\test.xlsx"
            Application.EnableEvents = True
            Cancel = True
        Else
            ' do nothing?
            ' Cancel=True
            ' or
            ' ThisWorkbook.Save
            'cancel = true
        End If

    Hope I did undestand it correct

    Wednesday, May 13, 2015 1:04 PM
  • Hi

    Not quite what I had in mind but thanks for the suggestion.

    What I am trying to do  is as follows:

    I have a file called TEST1.XLSM and I want this to always be the default name.

    If the user tries to change the name using SAVE AS then I want to prompt them  and ask if they want really  to change the name and if the answer is yes then the filename would be set to TEST1OFFLINE.XLSM  (they cannot set it to anything else) and if the answer is no then the filename stays the same i.e. TEST1.XLSM

    Does that make sense?

    thanks

    Peter

    Wednesday, May 13, 2015 1:13 PM
  • Hello again,

    I would sugest you make your custom "save as" dialoge.

    eg.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
       
        Dim dl As FileDialog
       
       
        
        Set dl = Application.FileDialog(msoFileDialogSaveAs)
       
        If dl.Show = True Then
       
            If dl.SelectedItems(1) <> ThisWorkbook.Path Then
                If MsgBox("really save?", vbOKCancel) = vbOK Then
                    Application.EnableEvents = False
                    Call ThisWorkbook.SaveAs(dl.SelectedItems(1))
                    Application.EnableEvents = True
                End If
            Else
                Application.EnableEvents = False
                Call ThisWorkbook.Save
                Application.EnableEvents = True
            End If
        End If
       
        Cancel = True
       
    End Sub

    Hope it helps.

    • Proposed as answer by Wouter Defour Monday, May 18, 2015 2:28 PM
    • Marked as answer by py1 Monday, May 18, 2015 2:42 PM
    Wednesday, May 13, 2015 1:53 PM