|
Prevent Saving a Workbook under a Different File Name
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
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- 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.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 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.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hello,
I'm using Excel 2007 and am looking for a VBA solution to prevent me from saving the Active Workbook with a specific name, i.e. "Master.xlsm" I've read the suggestions about making the file read-only but I'm looking for a different approach. The file could be saved to the current directory or to the user's directory but, regardless of where it is saved, cannot be named Master.xlsm. I feel that I was close but am just missing a piece or two of the answer. Any suggestions? Thanks
Hi,
To prevent resaving the worksheet I was using the code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox ("You Cannot Re-save Save this file the version is set")
End Sub
This is located in the "This Workbook" Object in the design view. I wanted to password protect this code, is there any way to do this. I have only been able to password protect code in the module, but this code cant go in the module because it is kicked off by a "Before Save" event.
Thanks
Excel 2003, windows xp
I want to prevent the user from saving a workbook when certain data are missing. When a user clicks the save button or try to use the Save As from the file menu to save a file I would like a message box to pop-up and let the user know that certain data is missing so they can go back and enter the missing data, however, if all the data are present allow the save to proceed.
Hope this explains the problem well enough.
Thanks
Basically I have some templates setup that are designed to have data copy/pasted from our computer systems, then you run a macro to format the data and do some other tricks.
These templates are designed for a variety of users, and usually once every few weeks someone screws the template up and saves over it-rendering it useless. I of course keep archived backups that I use to replace these files, but I was wondering if there was a way to setup the workbooks so you could restrict people from saving over the template-instead when you go to save you have save as something else...
Can this be done?
Hi,
I'm using the code below to save & view pdf copies of worksheets.
Code:
Sub PDFINV01_Click()
Dim strPath As String, wsName As String
strPath = ActiveSheet.Range("R6").Value
wsName = ActiveSheet.Range("R7").Value
Application.ScreenUpdating = False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & wsName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
The code works OK but I would like to extend it so that a "Do you want to overwrite existing file' message is generated if the filename already exists. Suggestions/assistance appreciated.
Thanks in advance.
Glenn
Hi,
I have and excel file, where you can find different comments inside cells, but whenever i click save (CTRL+S), my name changes to Author automatically.
I checked in:
File > Options > General > Username (it's my user pjeterd), but this is happening all the time i hit TRL+S.
I have Office 2010, please help!
Thanx in advance
Hope someone can help
im trying to create a private sub worksheet macro whereby if the user saves the file
it automatically de-selects any filters in the worksheet "SIGNALLING" but whilst
leaving the auto filter on
effectivly going through each column and selecting "all" from the drop down.
i need to have it in as a worksheet macro not a workbook macro, as when i copy the file - i lose the workbook code in the newly created file.
one option offered is to run a code which deletes all the unwanted tabs thus retaining all the code.
the issue with this though is that this can only be done as a "save as" and as it needs to produce 10 different copies containing different worksheets it would mean having to go back into the master file and running a separate macro.
alternativly is there a way i could create a worksheet code which puts up a message in the event of a user trying to save the file whilst their is a filter selected?
thanks
Hi,
Once every day at a specific time, I am opening an excel workbook as a scheduled task and then running a macro activated with Auto_Open. I want to save the file as part of the macro but want the name to change to indicate the date that the workbook was opened and the macro activated.
For example, I want the workbook to be saved as "Daily Carriage Analysis 05-04-2011" when saved on the fifth of April 2011 and as "Daily Carriage Analysis 06-04-2011" when saved on the sixth of April 2011 etc.
I can build the file name (including the folder path) in a cell in the work book as part of the macro but just cannot use that built name to save the file.
Is this possible?
Any help, much appreciated.
Many thanks.
|
|
