AutoComplete Read Only Excel xlsm File Name SaveAs

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

If ThisWorkbook.Name = "Test.xlsm" Then
    Sheets("Sheet1").Select

    If Not IsEmpty(A1.Value) Then

        MsgBox "This workbook is 'read only' Please rename this workbook."
        strName = "Please enter a new file name."
    ThisPAth = Application.ActiveWorkbook.Path
        ThisFile = Range("B1").Value
        ActiveWorkbook.SaveAs Filename:=ThisPath & ThisFile & ".xlsm", FileFormat:=52, CreateBackup:=False
    Else
        MsgBox "Cancelled."
    End If

End If
End Sub

      

        
        
        
      

    

I have a password protected workbook (Test.xlsm) that is purely for data entry. When the user opens the workbook as read-only, enters data, and then exits the workbook / template, I want the SaveAs dialog to automatically pop up so that the A1 content of Sheet1 is the "recommended" filename, which is auto-filled in the SaveAs box.

I thought that if I hooked on the BeforeSave function, I could declare this path / filename, but alas, that won't work. The autocomplete box displays "Copy of Test.xlsm". I don't even think he sees the above code.

How can I autocomplete this field with the desired name. Thank.

------------ Update ------------------

Rewrote the code below, but it still doesn't delay the default dialog on save. Perhaps I am misunderstanding the Workbook_BeforeSave function. I thought it was automatically called whenever the file is saved. I never want the user to save a file named Test.xltm (I changed the file to template to make sure it matters), but prompt the user to rename the file to B1 for standardization reasons. The code does not get automatically. I was able to get similar code to work if I invoke it by running a macro from the Quick Access Toolbar, for example, but cannot get it to automatically start when the user selects Close, Save, or Save As from the dropdown menu. " File".

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim saveDialog As FileDialog
Debug.Print "Hello"
Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)

If ThisWorkbook.Name = "Test.xltm" Then
    Application.EnableEvents = False
    Debug.Print "Save as"
    Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)
    With saveDialog
        .InitialFileName = "foo.xlsm"
        .Show
    End With
    Application.EnableEvents = True
Else
    Debug.Print "Cancel"
End If
End Sub

      

        
        
        
      

    

The template is password protected and opened as read-only by the user, so the SaveAs dialog should always open after exiting / saving / saving as. Right? And shouldn't Workbook_BeforeSave always be called under this circumstance?