BeforeSave VBA Save As Dialog Help

[letswriteafairytale]

I have a VBA that works ALMOST how I want it to work.

The code below works, only thing is, after saving the file ANOTHER save as dialog box opens. I don't want a second save as dialog box to pop up AFTER the file has already been saved in whatever file type they picked.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MsgResult As Integer

MsgResult = MsgBox("Please use the 'SAVE PROPOSAL' button at the top of the bid proposal. Click OK to return to the bid proposal. Click CANCEL to save as a different file type", _
    vbOKCancel, "STOP! This workbook needs to be saved as a Macro-Enabled workbook.")

Select Case MsgResult

    Case vbOK
            Cancel = True
            
    Case vbCancel
         With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 2
        If .Show Then
            ActiveWorkbook.SaveAs filename:=.SelectedItems(1), _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    End With
End Select
End Sub

 

[RoryA]

You need to disable events if you are going to save in a BeforeSave event:

Application.enableevents = false
            ActiveWorkbook.SaveAs filename:=.SelectedItems(1), _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
application.enableevents = true

Note: you should really have an error handler in case the save fails, so that events are always turned back on.

 

[John_w]

The .Show, if True, does the Save, so you don't need the ActiveWorkbook.Save (which causes the second Save dialogue to appear).

Change it to:

        If .Show Then
            Cancel = True
        End If

 

[letswriteafairytale]

Rory A & John W, both of those solutions worked.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MsgResult As Integer

MsgResult = MsgBox("Please use the 'SAVE PROPOSAL' button at the top of the bid proposal. Click OK to return to the bid proposal. Click CANCEL to save as a different file type", _
    vbOKCancel, "STOP! This workbook needs to be saved as a Macro-Enabled workbook.")

Select Case MsgResult

    Case vbOK
            Cancel = True
            
    Case vbCancel
    Application.DisplayAlerts = False
With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 2
        If .Show Then
            Cancel = True
                Application.EnableEvents = False
        Application.EnableEvents = True
Application.DisplayAlerts = True
        End If
    End With
End Select
End Sub

I just have one more question. How can I get the code to work ONLY for SAVE AS. It currently pops us for Save & Save As, I only want it to pop up when someone clicks save as.


Thanks for the help!

 

[RoryA]

You can test for that with:

If saveasui then

which will only be true if it is a Save As, not a Save.

 

[letswriteafairytale]

You can test for that with:

If saveasui then

which will only be true if it is a Save As, not a Save.

I thought that was the code. Took a few tries to get it in the correct spot, but it works perfect now.

Thank you!