Excel - Allows SaveAs Only

[Hack]

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.

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?

[Siddharth Rout]

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.

[Hack]

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

[Siddharth Rout]

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...

[Hack]

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

[RobDog888]

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

[Siddharth Rout]

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

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...

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

[Hack]

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.

[Hack]

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...

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?

[RobDog888]

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.

[RobDog888]

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

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

[Siddharth Rout]

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

[Hack]

There is no automation involved.

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

[Siddharth Rout]

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...

[Hack]

This is what I have for code in Sheet1

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.

[Hack]

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. )

[Siddharth Rout]

This is what I have for code in Sheet1

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...

[RobDog888]

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

[Siddharth Rout]

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

[Siddharth Rout]

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...

[Hack]

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?

[RobDog888]

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.

[Hack]

I have a BeforeSave sub and an BeforeClose sub.

Is there such as thing as an AfterSave sub?

[Siddharth Rout]

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...

[Hack]

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?

[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.

[RobDog888]

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.

'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.

[Siddharth Rout]

Hi Hack

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

'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...

[Siddharth Rout]

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...

[RobDog888]

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.

....

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

[Siddharth Rout]

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

[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

[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

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

[Siddharth Rout]

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

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 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?

[RobDog888]

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 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.

[Siddharth Rout]

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"

[RobDog888]

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.

[Siddharth Rout]

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.

[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

[Siddharth Rout]

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

Yes you do have a point there.