Forcing user to Rename Workbook.

R

Rollin_Again

Sorry about double posting this problem but I realized after submitting
my post that it should have been posted to this group instead of the
programming group. I have requested that the orignal post be deleted.
Please help, I need a quick solution!


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

Is it possible to add some code to the Workbook_BeforeSave event and
determine whether the file is being saved with the original filename
and if so, force a SaveAs something else instead?

I am not interested in creating a template so please do not offer this
as a solution.


Rollin
 
M

mzehr

Hi Rollin
Have you tried to set the properties to Read Only?. Once
you have saved the worksheet they way you want it, use
Windows Explorer, right click on the file, select
properties, and on the General tab, check the Read Only
box.

HTH
 
B

Bernie Deitrick

Rollin,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If SaveAsUI Then Exit Sub
If ThisWorkbook.Name <> "Protected Original Name.xls" Then Exit Sub
MsgBox "Save this under a different name!"
Cancel = True
End Sub

Note that this alone won't prevent the user from renaming the workbook, then
resaving it as the original name. You should make the original read-only to
protect it from those cases.

HTH,
Bernie
MS Excel MVP
 
D

Dave Peterson

How about making it a template?

Oh, wait.

How about making it readonly. Either in windows explorer (rightclick and choose
properties)

or even via a File|SaveAs|Tools|General Options|and give it a password to
modify.

Then they'll have to open it readonly unless you share the password (or change
the attribute).
 
R

Rollin_Again

Bernie and Dave,

Thanks! I will explore both methods and see which one works best for
me. Just by looking, I don't think Bernie's VBA solution is quite what
I need but I'll give it a try. Cheers to both of you!

Rollin
 
Top