Getting Userform Command Buttons to Work with a BeforeSave VBA Macro

[letswriteafairytale]

I have created a userform with 3 buttons, "Save as .XLSM", "Save as .PDF" and "Cancel"

What I would like is for this command box to pop up when we go to save the document (click on save as > browse)

The buttons work just fine every time someone hits Save as .PDF.

BUT, it will only work once if someone chooses save as .XLSM, it will save as .xlsm, then when you go to save again, the save as dialog box opens, but doesn't actually ever save again.

Here is the BeforeSave VBA:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
    Cancel = True
    UserForm1.Show
End Sub

Here is the UserForm1 code:

Private Sub CommandButton1_Click()
Call Save_as_XLSM
End Sub

Private Sub CommandButton2_Click()
Call Save_as_PDF
End Sub

Private Sub CommandButton3_Click()
Call Cancel
End Sub

Private Sub Label1_Click()

End Sub

Private Sub Save_as_XLSM()
 Dim ws As Sheet1
    Dim filename As String
    Dim saveAsDialog
    Dim savePath As Variant

    Set ws = ThisWorkbook.ActiveSheet

saveAsDialog = Application.GetSaveAsFilename( _
    filefilter:="Macro-Enabled Workbook (*.xlsm), *xlsm", InitialFileName:="", Title:="Please choose location to save this document")
    
  If saveAsDialog <> False Then
        ActiveWorkbook.SaveAs filename:=saveAsDialog, FileFormat:=52
        Exit Sub
    End If
Unload Me
End Sub


Private Sub Save_as_PDF()
Dim ws As Sheet1
    Dim filename As String
    Dim saveAsDialog
    Dim savePath As Variant
 
 Set ws = ThisWorkbook.ActiveSheet

saveAsDialog = Application.GetSaveAsFilename( _
    filefilter:="PDF Files (*.pdf), *pdf", InitialFileName:="", Title:="Please choose location to save this document")

  If saveAsDialog <> False Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=saveAsDialog, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        Exit Sub
    End If
Unload Me
End Sub

Private Sub Cancel()
Unload Me
    End
End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_Click()

End Sub

 

[Logit]

Paste in ThisWorkbook module :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    UserForm1.Show
End Sub

Paste in a Regular module :

Private Sub Save_as_XLSM()
    Dim saveAsDialog As Variant

    saveAsDialog = Application.GetSaveAsFilename( _
        FileFilter:="Macro-Enabled Workbook (*.xlsm), *.xlsm", _
        InitialFileName:="", _
        Title:="Please choose location to save this document")
    
    If saveAsDialog <> False Then
        Application.DisplayAlerts = False ' Suppress confirmation dialog
        ActiveWorkbook.SaveAs Filename:=saveAsDialog, FileFormat:=52
        Application.DisplayAlerts = True
        ThisWorkbook.Saved = True ' Mark workbook as saved
    End If
    Unload Me
End Sub

Untested here. Let me know if it works.

 

[letswriteafairytale]

Paste in ThisWorkbook module :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    UserForm1.Show
End Sub

Paste in a Regular module :

Private Sub Save_as_XLSM()
    Dim saveAsDialog As Variant

    saveAsDialog = Application.GetSaveAsFilename( _
        FileFilter:="Macro-Enabled Workbook (*.xlsm), *.xlsm", _
        InitialFileName:="", _
        Title:="Please choose location to save this document")
   
    If saveAsDialog <> False Then
        Application.DisplayAlerts = False ' Suppress confirmation dialog
        ActiveWorkbook.SaveAs Filename:=saveAsDialog, FileFormat:=52
        Application.DisplayAlerts = True
        ThisWorkbook.Saved = True ' Mark workbook as saved
    End If
    Unload Me
End Sub

Untested here. Let me know if it works.

Tried it in the UserForm & a regular module (wasn't sure if thats what you meant by "regular module", so I did both), didn't work. Same issue, save as dialog comes up and lets me try and save, but nothing actually saves.

 

[Logit]

Please post a copy of your workbook to a download site (i.e., Dropbox.com) . Do not include confidential information.
Provide link for download.

 

[letswriteafairytale]

Please post a copy of your workbook to a download site (i.e., Dropbox.com) . Do not include confidential information.
Provide link for download.

Dropbox

www.dropbox.com

Not sure if it could be the issues, but there is a hidden sheet.

I had to remove the beforesave VBA in order to save, so that will need to be added back in.



Thanks!

 

[Logit]

This is the basic coding for saving as .xlsm .

Private Sub Save_as_XLSM()
    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 2
        If .Show Then
            ActiveWorkbook.SaveAs filename:=.SelectedItems(1), _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    End With
End Sub

It works for multiple savings one at a time.

 

[letswriteafairytale]

This is the basic coding for saving as .xlsm .

Private Sub Save_as_XLSM()
    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 2
        If .Show Then
            ActiveWorkbook.SaveAs filename:=.SelectedItems(1), _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    End With
End Sub

It works for multiple savings one at a time.

That works!

Thank you. Last question. Do you know what code to use to prevent autosave from automatically turning on in the new save?

 

[letswriteafairytale]

That works!

Thank you. Last question. Do you know what code to use to prevent autosave from automatically turning on in the new save?

Nevermind, figured it out!

ThisWorkbook.AutoSaveOn = False before exitsub

Thank you for the help! Been working on these for a while now!

 

[Logit]

Hmmm ... I don't see that anywhere in your code. Where was it located ?

I believe you would want to remove this macro completely :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    UserForm1.Show
End Sub

 

[letswriteafairytale]

Hmmm ... I don't see that anywhere in your code. Where was it located ?

I believe you would want to remove this macro completely :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    UserForm1.Show
End Sub

Okay, yeah it does seem to be a problem with the UserForm IN the BeforeSave Macro. Because the command box works just fine when testing the UserForm. Its when its being called at the BeforeSave where the issues happen.

There's no way to correct that? All I want is to be able to ONLY have 2 save as options (or 3 options if you include XLTM) for XLSM or PDF.

I've tried a few codes before making the UserForm, but only the XLSM would work, not the PDF. I don't NEED a UserForm, I just thought that would be the solution to get both options.