MsgBox when saving file. VBA code doesn't work

[TimvMechelen]

Hi all,

Can somebody tell me why this VBA code doesn't work:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
MsgBox "Saved", vbOKOnly + vbInformation
ElseIf SaveAsUI = False Then
MsgBox "Not Saved!", vbOKOnly + vbCritical
End If
End Sub

When saving I want a msgbox that say "Saved"
When the saving failed I want a msgbox that says "Not Saved!"

Thanks for your help

 

[igold]

Hi,

Not 100% sure what your requirements are but from looking at your code perhaps this will satisfy them.

As to why your code does not work. I think it is because the trigger is BeforeSave and also I think you are using the SaveAsUI incorrectly.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success = False Then MsgBox "Not Saved!", vbOKOnly + vbCritical
    If Success = True Then MsgBox "Saved", vbOKOnly + vbInformation
End Sub

HTH

igold

 

[mikerickson]

The event is BeforeSave, so when it runs, it is not known whether the Save failed or not.

I'm not sure how the Save is being initiated (by code, by user selecting Save from the user interface) or why it might fail, but you might use

If Application.Dialogs(xlDialogSaveAs) Then
    MsgBox "save success"
Else
    MsgBox "save failed"
End If

 

[igold]

@Mike

If the user is initiating the save via the user interface would you show me how you would write the code using the BeforeSave event. I tried a couple of times and the code would run and complete before the Save or SaveAs Dialog box popped up.

Thanks in advance for your time.

igold

 

[mikerickson]

If the user initiates the Save from the UI, there is no way that the BeforeSave event will notify you of a failed attempt to save. The BeforeSave event runs before the file is saved.

What reasons do you anticipate for the Save failing?

 

[TimvMechelen]

I don't anticipate reasons for the Save failing. But i wanted to replace the MsgBox to something that prevent my .XLSM to overwrite the same file. Thats why i wanted to use the BeforeSave event, and that isn't working.

Thank you for your time

 

[TimvMechelen]

Hi,

Not 100% sure what your requirements are but from looking at your code perhaps this will satisfy them.

As to why your code does not work. I think it is because the trigger is BeforeSave and also I think you are using the SaveAsUI incorrectly.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success = False Then MsgBox "Not Saved!", vbOKOnly + vbCritical
    If Success = True Then MsgBox "Saved", vbOKOnly + vbInformation
End Sub

HTH

igold

When I use your code I get the message, But as I replied to mikerickson

I don't anticipate reasons for the Save failing. But i wanted to replace the MsgBox to something that prevent my .XLSM to overwrite the same file. Thats why i wanted to use the BeforeSave event, and that isn't working.

Thank you for your time

If I change the "Workbook_AfterSave" to "Workook_BeforeSave" it doesn't work anymore.

Thank you for your time

 

[TimvMechelen]

@ mikerickson

Sub BeforeSave()
    If Application.Dialogs(xlDialogSaveAs) Then
        MsgBox "save success"
        
    Else
        MsgBox "save failed"
        
    End If
    
End Sub

This code doesn't do anything when i press the save button

 

[mikerickson]

The Application.Dialogs code was not intended for the BeforeSave event, it was intended to go in a normal sub, but would return whether the Save was successful.

It sounds like your ultimate goal is to prevent the user from saving a workbook unless they change the name.

This code might work for you. Change the "Workbook1.xlsm" to the name of your file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim myFullPath As String
    Dim newPath As String
    
    If (ThisWorkbook.Name = "Workbook1.xlsm") And Not (OKFlag) Then
        Rem this is the original workbok
        If SaveAsUI Then
            Cancel = True
            myFullPath = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name
            newPath = Application.GetSaveAsFilename(myFullPath)
            If newPath = "False" Then Exit Sub: Rem canceled
            If LCase(myFullPath) = LCase(newPath) Then
                MsgBox "You must change the name or location of this file"
            Else
                MsgBox "all good"
                OKFlag = True
                MsgBox myFullPath & vbCr & newPath
                ThisWorkbook.SaveAs newPath
            End If
        Else
            MsgBox "You cannot save this workbook unless you change its name"
            Cancel = True
        End If
        
    End If
    OKFlag = False
End Sub

 

[TimvMechelen]

It sounds like your ultimate goal is to prevent the user from saving a workbook unless they change the name.

This is exactly what i mean yes! I'll try the code in a few minutes, thank you!