Prevent Saving a Workbook under a Different File Name

Edit
Add to Favorites
Author:
This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can only be saved under its current file name. However, the excel file can be saved in a different directory or different file location or drive. This macro only prevents one from changing the file name.

Where to install the macro:  ThisWorkbook

Excel Macro to Prevent Saving a Workbook under a Different File Name

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

'This macro prevents saving the excel file (workbook) under a
'different name.
'However, the file can be saved in a different location, but
'still under the same file name.
'
'This MUST be placed in "ThisWorkbook" and NOT in a Module.
'
Dim NamePath As String
Dim strName As String
Dim lFind As Long

   If SaveAsUI = True Then
      Cancel = True
      With Application
          .EnableEvents = False
      NamePath = .GetSaveAsFilename
      strName = Mid(NamePath, InStrRev(NamePath, "\", -1, vbTextCompare) + 1, 256)

  If NamePath = "False" Then
     .EnableEvents = True
                Exit Sub
      ElseIf strName <> Me.Name Then
                MsgBox "You cannot save as another name"
                    .EnableEvents = True
                Exit Sub

      Else
      Me.SaveAs NamePath
                 .EnableEvents = True
            End If

      End With
   End If
End Sub





Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial: Tutorial showing you how to make a user enable macros in a workbook in order to view the w...
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Run a Macro when a User Does Something in the Worksheet in Excel
Tutorial: How to run a macro when a user does something in the worksheet, everything from selecting ...
Make a UserForm in Excel
Tutorial: Let's create a working UserForm in Excel. This is a step-by-step tutorial that shows you e...
Put Data into a Worksheet using a Macro in Excel
Tutorial: How to input data into cells in a worksheet from a macro. Once you have data in your macro...


How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.

Tutorial Details
Similar Content
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial: Tutorial showing you how to make a user enable macros in a workbook in order to view the w...
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course