VBA to override typed workbook name on save

[kgartin]

Is there a way to write a VBA code in a sheet that will override any name a user enters when a workbook is saved? I want the sheet to pull the name from a cell instead of accepting the name they type in because they never name the file properly.

 

[John_w]

Try this code in the ThisWorkbook module. If the user tries to 'Save As', the dialogue isn't displayed and instead the workbook is saved with the file name in cell A1 of Sheet1, in the same folder as the code workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If SaveAsUI Then
        Cancel = True
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Worksheets("Sheet1").Range("A1").Value & ".xlsm"
        Application.DisplayAlerts = True
        Application.EnableEvents = True
    End If
    
End Sub