How to prevent the user from saving a file to any directory except one specific location?

Question

I'm working on code (Excel 2016) to prevent the user from saving a file to any directory except one specific location. I'm using the BeforeSave event as follows:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim NamePath As String
    On Error GoTo Final
'    If Saving Then Exit Sub
    If SaveAsUI = True Then
        Cancel = True
        Application.EnableEvents = False
        NamePath = Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Name, FileFilter:="Excel Files (*.xlsm), *.xlsm")
        If NamePath = "False" Then Exit Sub
        If InStr(1, NamePath, "H:\SSV\WORK\Workcenter Safety Modification Tracking Files", vbTextCompare) = 0 Then
            MsgBox "You cannot save to another directory."
            GoTo Final
        Else
            Me.SaveAs NamePath
            GoTo Final
        End If
    End If
Final:
    Application.EnableEvents = True
End Sub

When I select File from the ribbon bar and then Save As, I end up here:

When I click the Save button my code assumes that I haven't already entered a new filename, so it prompts for a Save As name and location. This works, as long as the user isn't using File|Save As. Using a Save As Button from a Quick Launch or customized ribbon works.

My problem is that using File|Save As doesn't trigger the BeforeSave event until after you enter the new filename and/or select a new location and click the Save button on the File|Save As screen. In the File|save as screen, I can already select one or both of these, but the BeforeSave event has no knowledge of this, it's firing just as if I had clicked on a Quick Launch button and hadn't already entered the new name or selected a new location. The user enters the new file name/location, clicks Save and is prompted again for a new file name/location.

I'm looking for one of two solutions:

1. A different way to limit where a user can save a file.
2. A way to pass the file name/location from the above File|Save As screen into the BeforeSave event.

Answer 1

I may be wrong but when they select the new location it might change the current working directory - in which case using CurDir may help

Answer 2

Application.GetSaveAsFilename() will return save as file path which you selected in File-> Save As.

C# code is below

Object obj = Application.GetSaveAsFilename();
if(obj != null)
{
   string filePath = obj.ToString();
}

In VBA

Application.GetSaveAsFilename

Answer 3

Put a button on the sheet and force the user to use that exclusively.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If not DoingSave then
        Msgbox("You must save using the button")
        Cancel = True
    End If
End Sub

When the user saves using the button, set DoingSave to True before saving and set it to False afterward.