This article shows you How to prevent Save As with VBA.

How to prevent Save As with VBA

You can control your workbook’s events like opening, closing or saving by VBA. Events for Excel’s native objects are built in and ready to use. To prevent Save As event, the address is Workbook object’s BeforeSave event. VBA has a predefined subroutine for this event: Workbook_BeforeClose.

This subroutine has 2 arguments which can say you if user is in Save As menu and allows you to cancel the Save action. So, the logic is simple: If user in Save As menu then NOT allow to save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      If SaveAsUI = True Then Cancel = True

End Sub
 

Before continuing the codes here is a simple explanation about them. Firstly; you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

To prevent Save As

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

      If SaveAsUI = True Then Cancel = True

End Sub