Trying to make a Macro work with BeforeSave Event...

[omairhe]

Hello guys,

How are you? I was hoping if some one could guide me with the following code extracted from

Excel VBA - Save As Dialog Window - stop code from continuing if "cancel" is pressed?

I currently use the following code to force the user to save the file as a macro enabled workbook. Application.Dialogs(xlDialogSaveAs).Show , xlOpenXMLWorkbookMacroEnabled The problem is, if the...

stackoverflow.com

The code is altered to work automatically each time with Save or SaveAs button. However there are lot of hit and misses. There are times when save is prompted twice. or even when browsing for a location to save the cancel click msgbox shows up.

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

On Error Resume Next
 userResponce = Application.Dialogs(xlDialogSaveAs).Show("Test name", 52)
On Error GoTo 0
If userResponce = False Then
    MsgBox "Cancel clicked"
    Exit Sub
Else
    MsgBox "You saved file "
End If
End Sub

Could anyone please alter it in ways so it can work for intended purpose?

Thank you.

 

[AlphaFrog]

Without your code, if you select SaveAs, the user is prompted with the SaveAs dialog file name selector. Your code also calls the SaveAs selector. So the user is prompted twice.

The Cancel = True line cancels the first one and uses the one in your code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    Dim userResponce As String
    
    userResponce = Application.GetSaveAsFilename("Test name")
    
    If userResponce = "False" Then
        MsgBox "Cancel clicked"
        Exit Sub
    Else
        Application.EnableEvents = False
        ThisWorkbook.SaveAs userResponce
        Application.EnableEvents = True
        MsgBox "You saved file "
    End If
End Sub

 

[omairhe]

I tested your code and it worked fine but the execute specific filetype is missing which is 52 for a macro enabled workbook or 51 for plain excel workbook. at the moment there is none associated with "Test name".
further help will be appreciated.

Thanks.

 

[AlphaFrog]

It's a macro enabled workbook by default. I has code in it.

 

[omairhe]

It's a macro enabled workbook by default. I has code in it.

I actually needed the Excel workbook .xlsx file type extension. Did some research and the following script will do.

    userResponce = Application.GetSaveAsFilename("Test name", "Excel Workbook(*.xlsx), *.xlsx")

Thanks again.

 

[omairhe]

It's a macro enabled workbook by default. I has code in it.

The file is going to be write protected and will only allow for SaveAs. Hence the original macro enabled workbook stays intact .

 

[omairhe]

Here is my final code which works now because I was getting file format errors..but now after including FileFormat:=xlOpenXMLWorkbook to this Workbook.SaveAs, it runs as desired. Two things are achieved with the code below.
1. the user has a predefined name and extension set whenever save button is clicked. and
2. the links will break when user saves the file and will not break if the user clicks on cancel.

Due to the fact that file extension cannot be set to .xlsm (macro-enabled workbook) in first part of the code, hence there will be no chance of overwriting it accidentally with the default macro enabled one.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    Dim userResponce As String
    Dim ExternalLinks As Variant
    Dim x As Long
    Dim wb As Workbook
    Set wb = ActiveWorkbook


    userResponce = Application.GetSaveAsFilename("Sales Report - Month Year", "Excel Workbook(*.xlsx), *.xlsx")

    If userResponce = "False" Then
        Exit Sub
    Else
    wb.Unprotect "1234"
    wb.ActiveSheet.Unprotect "1234"
    ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(ExternalLinks) Then
    For x = 1 To UBound(ExternalLinks)
    wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
    Next x
    End If

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs userResponce, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End If
End Sub

Thank you AlphaFrog for your valuable and much appreciated code. : )

 

[omairhe]

I have to keep press the shift key before opening a macro workbook, just in case I need to change something. doing the Shift (pressed down) til the file opens up will not run any macros for the current session.