VBA Excel Crashes when .saveas called inside beforeClose on Mac

[Dnav]

Hey folks, interesting problem here. I have an order form that users fill out and send in using VBA. I must avoid the original file being saved over and prompt the user to saveas if they haven't saved a copy of the workbook since they opened it. I want to be able to give the user the option to save the workbook on close but if they haven't saved a copy yet, they're forced to use .saveas, otherwise just .save.

The problem is that when manualSaveAs() then MacGetSaveAsFilename is called from the beforeClose event, it crashes at the line

FName =  Application.GetSaveAsFilename(InitialFileName:=MyInitialFilename)

and does not finish the rest of the code.

The saveas window begins to appear then everything just shuts down with no error messages, just Microsofts error reporting prompt. The tricky part is that the code works as expected if run from the debugger outside of the beforeClose event. It also works fine inside beforePrint or as a button_click macro. I've also found the same crash problem happening if manualSaveAs() is called from inside the beforeSave event.

Got a lot of this code thanks to Ron de Bruin and his example code works fine, but just not in my code. Going on a few days of fruitless answer searching. I'm super stumped. This bug goes way beyond my short stack of vba know-how. Any help is appreciated.

Using Mac Lion 10.8.2, Excel for Mac 2011, VBA ver. 14.3.1 haven't tried this on the PC side yet.

Here's the relevant code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If check_Fields_Filled(4) = 2 Then
        Cancel = True
        Exit Sub
    End If
    
    If ThisWorkbook.Saved = False Then
        msg = MsgBox("Would you like to save before closing?", 1, "Save?")
        
        If msg = 1 Then
            If Worksheets("datasets").Range("W7").Value = False Then
                Call manualSaveAs
            Else
                Application.EnableEvents = False
                ThisWorkbook.Save
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

Public Sub manualSaveAs()
    Dim MyInitialFilename As String
    Dim fileSaveName As Variant
    Dim isSaved As Boolean
    
    If Worksheets("Purchase Order Form").Range("I6").Value = "" Then
        MyInitialFilename = "EWSPO-" & Format(Now, "yymmdd hh-mm-ss")
    Else
        MyInitialFilename = "EWSPO-" & _
        Worksheets("Purchase Order Form").Range("I6").Value & "_" & Format(Now, "hh-mm-ss")
    End If
    
    If Application.OperatingSystem Like "*Mac*" Then
        'is a Mac
        isSaved = MacGetSaveAsFilename(MyInitialFilename)
    Else
        'is a PC - NEEDS WORK
        fileSaveName = Application.GetSaveAsFilename( _
        InitialFileName:=MyInitialFilename, Title:="Save a Copy")

        If fileSaveName <> False Then
            ActiveWorkbook.SaveAs FileName:=fileSaveName, _
            FileFormat:=52, CreateBackup:=False
            Worksheets("datasets").Range("W7").Value = True
        End If
    End If
End Sub

Function MacGetSaveAsFilename(Optional MyInitialFilename As String) As Boolean
    'Ron de Bruin, 4-Nov-2012
    Dim FName As Variant
    Dim FileFormatValue As Long
    Dim TestIfOpen As Workbook
    MacGetSaveAsFilename = False

    'Call VBA GetSaveAsFilename
    'Note: InitialFilename is the only parameter that works on a Mac
    If MyInitialFilename = "" Then
        MyInitialFilename = ActiveWorkbook.Name
    End If
    FName = Application.GetSaveAsFilename(InitialFileName:=MyInitialFilename)

    'Find the correct FileFormat that match the choice in the "Save as type" list
    If FName <> False Then
        Select Case LCase(Right(FName, Len(FName) - InStrRev(FName, ".", , 1)))
            Case "xls": FileFormatValue = 57
            Case "xlsx": FileFormatValue = 52
            Case "xlsm": FileFormatValue = 53
            Case "xlsb": FileFormatValue = 51
            Case Else: FileFormatValue = 0
        End Select
        
        If FileFormatValue = 0 Then
            MsgBox "Sorry, unknown file extension"
        Else
         'Now we can Save the file with the xlFileFormat parameter
         'value that match the file extension

         'Error check if there is a file open with that name
         Set TestIfOpen = Nothing
         On Error Resume Next
         Set TestIfOpen = Workbooks(LCase(Right(FName, Len(FName) - InStrRev(FName, ":", , 1))))
         On Error GoTo 0

         If Not TestIfOpen Is Nothing Then
             MsgBox "There is a file open with that name, try again and use a different name.", _
                    vbOKOnly, "Sorry, can't save the file"
             FName = False
         End If

         'Save the file in the format you choose in the "Save as type" dropdown
         If FName <> False Then
             Application.DisplayAlerts = False
             Application.EnableEvents = False
             On Error Resume Next
             ActiveWorkbook.SaveAs FName, FileFormat:=FileFormatValue
             On Error GoTo 0
             Application.DisplayAlerts = True
             MacGetSaveAsFilename = True
             Application.EnableEvents = True
         End If
         End If
    End If
End Function

 

[starl]

Common issue. Remember - VBA on a Mac is not the VBA you know from PC.
I ran into the issue recently and came to the conclusion that the dialog box isn't the same as the PC one and we aren't meant to use VBA to manipulate it. If you can find some applescripting, you could try that. But I ended up not trying to configure the Mac dialog box.

I know - not the answer you wanted.. but I don't applescript.

 

[Dnav]

Thanks for the heads up. So you're suggesting using applescript in place of the Application.GetSaveAsFilename(...) call? Yeah, I'm no applescripter... There's got to be some way around this until Microsoft decides to update Mac VBA to function the same as Windows VBA... I'll look into an applescript solution though.

I would be way less prone to pursing a strictly VBA based solution if the function didn't work everywhere else. It's *so close*!

 

[starl]

I'm no expert on Mac VBA, but I'm thinking it's similar to API calls - only working on PCs.
You're trying to interact with a Mac dialog box...
Though.. really, I could be wrong. I mean - the code is from Ron.. I'd imagine he knows what he's doing. But I ran into the same issue as you..

 

[Dnav]

I think I found a way to do it with AppleScript. Will post the code if I get it working. Thanks for the tip starl.