Excel - Allows SaveAs Only

I'm trying to ensure some template spreadsheets do not get overwritten with user data, so I'm trying to Force as SaveAs only. If the user selects Save from the drop down menu, or hits the Save icon on the toolbar, then this sub does the trick.

Code:

---

Sub FileSave()
MsgBox "Please use SaveAs and give the document a new name.", vbOKOnly + vbInformation, "Saving Template Not Allowed"
End Sub

---

However, if they hit the big red X in the upper right hand corner, the following message is displayed, and they can select Yes, and overwrite the template. How do I prevent this messagebox from showing up and, if they close the workbook by hitting the red X, it simply exists without saving?

Hi Hack

The answer is the same as I had given you earlier. You can save the file in a read-only mode. This way they won't be able to overwrite the actual file until and unless they have the password.

Read-Only is an easily changed attribute that I've never depended on.

Quote:

---

Originally Posted by Hack

Read-Only is an easily changed attribute that I've never depended on.

---

But hack, it's not easy at all till you know the password...

I can't use a password. The department manager doesn't want passwords involved in the scenerio at all.

If they are templates them they shouldnt be able to be overwritten unless the user does a saveas and selects xlt template.

Quote:

---

I can't use a password. The department manager doesn't want passwords involved in the scenerio at all.

---

:lol:

Well in that case, Hack, would you like the user not to be able to exit by clicking on 'X' button? Each time the user clicks on the 'X' button, he will be prompted to do a Save As... If Yes then this should help...

vb Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not saveasUI = True Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

If you don't want the user to exit by clickin on the 'X' button then simply use the above code without the MSGBOX....

Hope this will satisfy the department manager :)

Quote:

---

Originally Posted by RobDog888

If they are templates them they shouldnt be able to be overwritten unless the user does a saveas and selects xlt template.

---

They are template spreadsheets created by me for them to use to "fill in the blanks", and then save the actual data file off under and appropriate name.

Quote:

---

Originally Posted by koolsid

:lol:

Well in that case, Hack, would you like the user not to be able to exit by clicking on 'X' button? Each time the user clicks on the 'X' button, he will be prompted to do a Save As... If Yes then this should help...

vb Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not saveasUI = True Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

If you don't want the user to exit by clickin on the 'X' button then simply use the above code without the MSGBOX....

Hope this will satisfy the department manager :)

---

What is: saveasUI?

Is that a variable I need to declare or Excel will yell at me?

If you save it as a xlt template file then you can eliminate your issues. If you use automation to open a new workbook you can create it based upon the template but if you dont then you can just distribute the xlt and when they go to double click / open it it wiill generate the new workbook based off of the template.

Quote:

---

Originally Posted by Hack

What is: saveasUI?

Is that a variable I need to declare or Excel will yell at me?

---

Its from the BeforeSave event. Set it depending on if you want the Save As dialog box to be displayed or not

Code:

---

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

---

Quote:

---

Originally Posted by Hack

What is: saveasUI?

Is that a variable I need to declare or Excel will yell at me?

---

Nah you do not need to declare it. The code below can be used to stop any users saving a Workbook as another name and another location. The code must be placed in the Private Module of the Workbook Object (ThisWorkbook). The fastest way to get there is to right click on the Excel icon, top left next to File and select View Code. It is in here you should place the code below and then save the Workbook. So long as the Workbook is opened with macros enabled the code will fire anytime any user tries to clcik on the 'X' button.

hope this helps...

edit: Ah Rob beat me to it :)

There is no automation involved.

This is all being done directly from Excel. No other development platform is being used.

Quote:

---

There is no automation involved.

This is all being done directly from Excel. No other development platform is being used.

---

I am not sure if I understand what you mean...

The above code is a part of excel i.e vba excel...

This is what I have for code in Sheet1

Code:

---

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not SaveAsUI = True Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

---

I do stuff on the spreadsheet and hit the big Red X.

I still get the message box asking me if I want to save my changes. I would prefer not to get that. I just want the spreadsheet to close with nothing saved.

When I think Office Automation, I think of Word or Excel of whatever being used through a VB6 or a VB.NET program as opposed to using strickly VBA.

The reason I'm having such difficulty with this is because the flippin' weasels won't let me use Vb6 or VB.NET - I have to use VBA (and I just learned how to spell VBA yesterday. :sick: )

Quote:

---

Originally Posted by Hack

This is what I have for code in Sheet1

Code:

---

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not SaveAsUI = True Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

---

I do stuff on the spreadsheet and hit the big Red X.

I still get the message box asking me if I want to save my changes. I would prefer not to get that. I just want the spreadsheet to close with nothing saved.

---

you shouldn't be getting that message. Where exactly are you pasting the above code...

The code should be in ThisWorkbook class instead of behind Sheet1.

ah I got it... you pasted it in sheet 1....
Ok give me a moment and i will explain how to do it ...

Okay, Here it is...

I have kept it as simple as possible...

1) Click on Menu Tool=>Macros=> Visual Basic Editor.

2) On the left hand side you will see the sheets and the "Thisworkbook" icon. Double Click the "Thisworkbook" icon. The window that opens, you need to paste the code there...

Hope this helps...

edit: I am also attaching a picture which will expalin it more clearly...

Quote:

---

Originally Posted by RobDog888

The code should be in ThisWorkbook class instead of behind Sheet1.

---

That was it boys and girls. I moved it to "ThisWorkBook" and it seems to be working.

Next question. This doesn't have anything to do with this project. It is a curiousity question on my part.

In terms of code, what goes on the sheet as opposed to what goes in the workbook? How do you make that determination?

Its nothing major, just like how you decide if you have code for a form in VB 6. Do you place it in a function in a module or behind the form.
ThisWorkbook would be where you place code that pertains to the entire workbook and behind a sheet if its specific to that sheet only.

I have a BeforeSave sub and an BeforeClose sub.

Is there such as thing as an AfterSave sub?

Quote:

---

Originally Posted by Hack

I have a BeforeSave sub and an BeforeClose sub.

Is there such as thing as an AfterSave sub?

---

No hack

In fact if you click as shown in the picture, you can see all the available event for workbook.

hope this helps...

Anyone have a reasonably tall bridge that I might borrow from which I may hurl myself?

Please refer to the first post in this thread.

Now, my FileSave sub isn't firing when I try to do a normal old ordinary Save. Naturally, I don't want this feature available. The only way they should be able to save it is by doing a Save As

Does this sub belong on the worksheet or in the workbook or do I need something else entirely?

It will be in the ThisWorkbook class and it will be "Workbook_BeforeSave" event and not the beforeclose event with would fire after it was saved or not.

I think this is your current code so it would look like this in your ThisWorkbook class....

Remember there is no "SaveAsUI" variable in the BeforeClose event. ;)

Code:

---

'Behind "ThisWorkbook" class
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

---

I would add more code to prevent it from being saved as the same file name but they could still rename the file and save again to bypass it. There is another workaround but let me Hack up some sample code for you.

Hi Hack

Further to what Rob said, Paste ALL THESE codes in the workbookevent

vb Code:

'The Following Code will disable CTRL +S and File=>Save
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
If SaveAsUI = False Then
 
    Cancel = True
 
    MsgBox "You cannot save this workbook.  Use Save As"
 
End If
 
End Sub
 
'This code will disable the Save Button
'remember to reenable it later
 
Private Sub Workbook_Open()
 
    Application.CommandBars("Standard").FindControl(ID:=3).Enabled = False
 
End Sub
 
'This code will make sure that the user doesn't save
'when he or she presses the 'X' Button
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not SaveAsUI = True Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

hope this helps...

Quote:

---

Originally Posted by RobDog888

It will be in the ThisWorkbook class and it will be "Workbook_BeforeSave" event and not the beforeclose event with would fire after it was saved or not.

---

Hi Rob

Hack will have to use both one for the File=>Save and Ctrl S and one for the 'X' button on the top right....

EDIT: I have included all the three codes that Hack will have to use...

Actually disabling the "Save" menu isnt my recommendation as all they have to do is do a save as and save as the same file name, overwritting the workbook you are trying to prevent them from doing.

This is going to take some creative thinking/coding. :D

....

Are macros going to be a guarenteed to be enabled? If not then all the code wont help you.

Quote:

---

Originally Posted by RobDog888

Are macros going to be a guarenteed to be enabled? If not then all the code wont help you.

---

Absolutely... There is no doubt about it. The only alternative was to save it as readonly but seems like we don't have that option open ;)

Rest I have included all the codes in post 28, that is necessary to prevent a user from overwriting the original file :D

Well not exactly as you can overwrite the original file as well as do a save from the toolbar button and shortcut key Alt+ S :D

If you do something like this it will prevent them from saving it as the original file name. Granted they can rename the original file but how will they know that is the workaround :D

Code:

---

Option Explicit
'Change the filename comparison to reflect your filename your sending the workbook out as.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True And ThisWorkbook.Name <> "Book1.xls" Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

---

Quote:

---

Originally Posted by RobDog888

Well not exactly as you can overwrite the original file as well as do a save from the toolbar button and shortcut key Alt+ S :D

---

I tried using Alt+S, Ctrl+S, Alt+F=>S, Clicking on the "X" Button but it didn't let the user save the file :confused: Also the save button is disabled. So I don't see any other way a user could save it... have you tried after pasting all the three codes in the workbook and then saving the workbook once?

I know about disableing the menu Save but as I wrote you didnt disable the toolbar too which the users use primarily. Its not a big deal to do.

But more importantly they can still do a save as and overwrite as that would be their default thinking that they just must do a "Save As" to save the current file for whatever reason :lol: Too much trouble to do all that when doing it my way is easier and less code/cmplications.

Preventing the overwrite at the file level by comparing the filename is a bit better IMO as they wont know that the workaround is to rename the file in explorer. They will just save it as another name and that would be what Hack wants in the end.

You are right Rob but remember what hack also wanted in the intial post was that they should not be able to use the "X" button. And hence the before close event ;)

Seems like this thread will end up in the codebank title "Various ways on how to disable 'Save' in Excel" :lol:

Well I already have a few FAQ items on commandbars but not too many demands for preventing saving etc.

Plsu, dont forget that by disabling the save you are globally disabling the save for all workbooks and not just a specific workbook. ;)

Quote:

---

Originally Posted by RobDog888

Plsu, dont forget that by disabling the save you are globally disabling the save for all workbooks and not just a specific workbook. ;)

---

Exactly. that's why I mentioned in my code above that it needs to be re-enabled when you exit the workbook.

But if they open another workbook before closing/saving as they cant save that one either until the original one is saved as and closed. This is why I was staying away from that solution as it can get hairy fast :D

Quote:

---

Originally Posted by RobDog888

But if they open another workbook before closing/saving as they cant save that one either until the original one is saved as and closed. This is why I was staying away from that solution as it can get hairy fast :D

---

Yes you do have a point there.

Quote:

---

Originally Posted by RobDog888

I would add more code to prevent it from being saved as the same file name but they could still rename the file and save again to bypass it.

---

I'm not concerned about that. First of all, the type of customers I have for this probably don't know how to do that, and second if they did, then so be it. They are screwing themselves. You can make something idiot proof, but you can't make it moron proof.

I don't want to disable any buttons. I want to alter the functionality. I want Save to automatically act as though Save As was selected, and I do not want Excel prompting the users to save their work. If they exit without doing a Save As they will lose what they did so they will have to do it all over again. Hopefully they will be bright enough to only do that once.

Quote:

---

Originally Posted by RobDog888

If you do something like this it will prevent them from saving it as the original file name. Granted they can rename the original file but how will they know that is the workaround :D

Code:

---

Option Explicit
'Change the filename comparison to reflect your filename your sending the workbook out as.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True And ThisWorkbook.Name <> "Book1.xls" Then
        MsgBox "Please use Save As To Save this File"
        Cancel = True
    End If
End Sub

---

---

Using this code, even when I click File/Save As, I get the message box indicating I should use Save As.

@Hack, try this code, it may help (I haven't fully test it.) No need to disable anything.

Please note that if user Disable Macro or get into Design Mode then he still can Overwrite the Template.

It's better save the file as Excel Template file type (.xlt) it will have less accidents.

On design the template, save it to another filename then rename the file to what you specify in 'OriginalTemplate' in the code.

Code:

---

'-- In ThisWorkbook module
Option Explicit

Const OriginalTemplate = "C:\Users\Hack\VBForums\MyTemplate.xlt"
Dim SaveByCode As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  If Me.FullName = OriginalTemplate Then
      If Me.Saved = False Then
        If ForceSaveAs() = "False" Then
            Saved = True '-- trick the Workbook, let it thinks already saved.
        End If
      End If
  End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim NewFullName As String
 
  If SaveByCode = True Then
      SaveByCode = False
  ElseIf Me.FullName = OriginalTemplate Then
      If SaveAsUI = False Then
        ForceSaveAs
        Cancel = True
      ElseIf NewFullName = OriginalTemplate Then
        MsgBox "Please choose another file name", vbExclamation, _
                "Cannot Overwrite Template File"
      End If
  End If
End Sub

Private Function ForceSaveAs() As String
  Dim NewFullName As String
  Do
      NewFullName = Application.GetSaveAsFilename
      If NewFullName <> OriginalTemplate Then Exit Do
      MsgBox "Please choose another file name", vbExclamation, _
            "Cannot Overwrite Template File"
  Loop
  If NewFullName <> "False" Then
      SaveByCode = True
      On Error Resume Next
      Me.SaveAs NewFullName
      If Err <> 0 Then NewFullName = "False"
      SaveByCode = False
  End If
  ForceSaveAs = NewFullName
End Function

---

I assume the ForceSaveAs function would go in the Workbook not the Worksheet right?

Two things:

I like the fact that if you hit File/Save, the Save As dialog appears. That is good.

However, if I hit the Red X in the upper right hand corner, I'm still prompted with Excel's message asking me if I want to save my changes. If I click Yes, it just saves it, i.e., it overwrites the existing file with my new changes, which, of course, I don't want. How to I get a Save As dialog to appear in this scenerio?

Thats similar to my code suggestion to compare the file name as to know if they are trying to save over the original file name or not.

Should work. :)

I wrote the code in Post#42 at midnight (12:36 AM).

You can change _BeforeClose() to:

Code:

---

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim Rep As VbMsgBoxResult

  If Me.FullName = OriginalTemplate Then
      If Me.Saved = False Then
        Rep = MsgBox("Do you want to save the changes you made to workbook " _
                      & Me.Name & "?", vbExclamation + vbYesNoCancel)
        If Rep = vbCancel Then
            Cancel = True
        ElseIf Rep = vbNo Then
            Saved = True '-- trick the Workbook, let it thinks already saved.
        ElseIf ForceSaveAs() = "False" Then
            Cancel = True
        End If
      End If
  End If
End Sub

---

and one small change in ForceSaveAs()

Code:

---

Private Function ForceSaveAs() As String
  Dim NewFullName As String
  Do
      NewFullName = Application.GetSaveAsFilename("")
      ... ...

---

I will give another approach in next post that will be much simpler.

This is another approach, that is much simpler and much easier:

On Open the workbook will check if its fullname is the OriginalTemplate name then turn ReadOnly flag on, otherwise allow ReadWrite as normal.

I repeat the suggestion in the other post: It will be better to save your template with an Excel Template file type (.XLT), however this is not required.

Do not combine with the code from the other post.

Code:

---

'-- In ThisWorkbook module
Option Explicit

Private Sub Workbook_Open()
  Const OriginalTemplate = "C:\!Tools\MyTemplate.xlt"
 
  On Error Resume Next
  If Me.FullName = OriginalTemplate Then
      Me.ChangeFileAccess Mode:=xlReadOnly, Notify:=False
  Else
      '-- error may happen here so resume next
      Me.ChangeFileAccess Mode:=xlReadWrite
  End If
  Saved = True
End Sub

---

:) Haha! :) I hope I can get 50 reputation points from this post.

i might be missing something here, but why don't you save your workbook as a workbook template (.xlt),
all the code and data will be included in any workbook opened based on that template, the user just open a new workbook each time, but no changes will be made to the template no matter what they do as far as saving the workbook

pipped again........... lol

@pete, although I suggested the template should be saved with file type *.xlt, but even that it is not always the case. Users may be able to open the template file the same way as the developer to open it.
(Instead of double click on an .xlt file that will create a new file in the memory space without extension based on the template, users can use Open button or menu to select an .xlt and open it directly.)

It is an .xls file, and a .xls file it must stay (don't ask)

So, all solutions to this little problem I have to do in code.

Bottom line Hack is that in the save event you need to verify if its the original workbook filename as I suggested and if it is disallow the save. If they do a save as it will no longer be opened as your original workbook so future changes/saves will not overwrite the original.

Use the simple code in Post#47, Excel will take care of everything when the template becomes ReadOnly.

If you are not sure where you will put the template, you can verify on workbook's Name only instead of Full Name:

Code:

---

Private Sub Workbook_Open()
  Const TemplateName = "MyTemplate.xlt"
 
  On Error Resume Next
  If Me.Name = TemplateName Then
      Me.ChangeFileAccess Mode:=xlReadOnly, Notify:=False
  Else
      '-- error may happen here so resume next
      Me.ChangeFileAccess Mode:=xlReadWrite
  End If
  Saved = True
End Sub

---

If the original template has no code and you don't want to write any code in it:
You can save the original template with ReadOnly option that comes along with a write access Password.
On openning, users will be prompted with a message:

http://www.vbforums.com/attachment.p...1&d=1201211169

Any method above is not safe if user has a security access of Write or Modify to the folder or the template file, they can open other file then on Save As they can overwrite your template.

The best way is to apply system Security option on the file or the folder that is on a network drive, make it become ReadOnly.
Do not use ReadOnly option on General tab of file Properties that can be turned on or off by anyone.

I can't use an .XLT file - only an .XLS file

I recommend you to use Security option on the file or folder, I use this option for hundreds Excel files that I create weekly and put on several network folders.
It doesn't matter what extension you use.

I dont think that may apply here as its probably a distributed slx file?

Quote:

---

Originally Posted by RobDog888

I dont think that may apply here as its probably a distributed slx file?

---

Actually, it is a single spreadsheet that is used by mutliple people (it is set up as a Shared Workbook) that is house on a network drive.