Saving a Workbook: Forcing User to Rename before Saving

[Rollin_Again]

I have a workbook that several people at work will be using. I want t
keep the orignal workbook intact and force the users to rename th
workbook whenever they try to re-save it.

Is it possible to add some code to the Workbook_BeforeSave event an
determine whether the file is being saved with the original filename?


Rolli

 

[Pete Wright]

This should do the trick.

Sub Save()
Dim FName As Variant
ActiveSheet.Shapes("Rectangle 37").Select
Selection.CheckSpelling
CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:= _
False, AlwaysSuggest:=True, SpellLang:=1033
Range("A10").Select
FName = Application.GetSaveAsFilename( _
filefilter:="Excel files (*.xls),*.xls")
If FName = False Then
' user clicked cancel
Else
ThisWorkbook.SaveAs Filename:=FName
End If
End Sub

Pete

 

[David]

How about password protecting the document so the user has to rename
the file to save it?

 

[Rollin_Again]

Protecting the entire workbook seems to have no effect on whether or no
changes can be made and the workbook re-saved. I tried passwor
protecting the book and when I made changes to the cells I was able t
re-save the workbook under the original name.

When I password protect the sheet, the cells cannot be modified at all
This will not work since the users need to modify the cells an
re-save with a different name.


Rolli

 

[Fraggs]

Set the file to a read only file, when you set it to read only you ca
have the option of protecting the workbook from other users removin
the read only with a password. This still allows you to edit th
workbook but forces you to save it as a different file upon exit o
manualy clicking save

 

[kkknie]

Code
-------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
MsgBox "Please choose Save As to save this workbook.", vbInformation
Cancel = True
End If
End Su