BeforeSave VBA Save As Dialog Help

letswriteafairytale

New Member
Joined
Dec 23, 2024
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You need to disable events if you are going to save in a BeforeSave event:

VBA Code:
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.
 
Upvote 0
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:
VBA Code:
        If .Show Then
            Cancel = True
        End If
 
Upvote 0
Solution
Rory A & John W, both of those solutions worked.

VBA Code:
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!
 
Upvote 0
You can test for that with:

Code:
If saveasui then

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

Forum statistics

Threads
1,226,696
Messages
6,192,487
Members
453,727
Latest member
tuong_ng89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top