Save not allowed

Suggestion

Instead of saving your spreadsheet as .xls - excel spreadsheet, save it as .xlt - excel template. This way they will not be able to overwrite the original. Alternatively alter the settings of the spreadsheet to read-only, from the properties.

George

Didn't work, they could still save as template.

Thanks for the suggestion.. I'm dealing with people that need to be hand-held through this whole process.

I tried both suggestions, and I was unable to make the file read-only. And when I used a template, I hit save and it just replaced it. Any other options?

Re: Didn't work, they could still save as template.

When you save the template, choose the Save As option. Choose Options (Excel 97) or Tools>General Options (Excel 2000) and assign a Password to Edit. Then they can not save over the template, but can save the file as a new workbook.

Thanks for the suggestion.. I'm dealing with people that need to be hand-held through this whole process. I tried both suggestions, and I was unable to make the file read-only. And when I used a template, I hit save and it just replaced it. Any other options? : Instead of saving your spreadsheet as .xls - excel spreadsheet, save it as .xlt - excel template. This way they will not be able to overwrite the original. Alternatively alter the settings of the spreadsheet to read-only, from the properties. : George :

Becky, the following code will set the windows attributes to 'read-only.' You may want to create an input box to 'Save As' instead of using the ShowSave method.....You'll also need to test or add or replace the .xls extension, I haven't dealt with that here:

Sub Svr()

name = Range("e1")
On Error GoTo errorhandler
ActiveWorkbook.SaveAs Filename:=name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
SetAttr name, vbReadOnly

Exit Sub

errorhandler:

MsgBox ("Guess Again")

End Sub

Becky,

You probably could do this :

name = Range("e1") & ".xls"

If the user doesn't have the extension, you'll end up with file.xls.

If they do, you'll end up with file.xls.xls, while ugly, should still work.

Best of luck!

I had a similar problem with a packing list program that I created. There would sometimes be more than one packing list for each sales order, and I wanted to stop the overwriting of the files. The one difference here is that I do not bring up the "Save As" window. The save path is always the same and without the window there is no chance for the user to change it. This routine actually copies the active sheet to a new workbook with one sheet and then saves that workbook, then closes it and returns you to the original worksheet you started from. The reason I did that was because most of the workbooks I am using contain from 15-40 worksheets, and if you do a save of the workbook the file sizes become an issue. An average workbook save in my case was around 200K file size. Using the single sheet method the average file size is around 38K. I hav noted the areas of the macro and hopefully if this doesnt exactly meet your requirements you will be able to change it accordingly.

Application.ScreenUpdating = False (stops screen flashing)
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("d8").Value (this is the cell I get the filename from)
On Error GoTo do_not_overwrite (if the file exists excel will display a message)
ActiveSheet.SaveAs Filename:="C:\packing lists\" & ThisFile & ".xls"
Application.DisplayAlerts = False(does not prompt the user for printing options, just prints the report)
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close
Application.DisplayAlerts = True (turns the excel alerts back on)
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("ChangingButton").Visible = True
Application.DisplayFormulaBar = True (the above 4 lines turn back on the menu bars and formula bars in excel which I turned off earlier in the macro)
Application.ScreenUpdating = True
Exit Sub

do_not_overwrite:
MsgBox "The filename already exists. Add a letter to the end of the sales order number then press the Save Packing List button again."
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True (This routine displays a user message and then closes the workbook and returns the user to the original workbook)