How do I stop people from saving a workbook as a particular file name?

[12Keys]

Hi,

can anyone help?


I want to stop users opening a particular workbook and then saving that

workbook as "master.xls".


So if they open the document called master.xls, if any save action is
invoked (i,e they click "save" or "save as") - they are prompted with a

message telling them to save as a different name (if they are about to
save it as master.xls).


If the autosave is on - is there anywhere I can use this to prompt them

to save as sat "dummy1.xls"? If not then, no matter. As long as they
cant save as "master.xls".


Does anybody know how to write the VBA code to do this? I know it needs

to go in the worksheet_Beforesave() event.


Many thanks,

 

[Zack Barresse]

Hi there,

Have you thought about making your file a template? This way it would
automatically save as a different name (i.e. master1.xls, master2.xls, etc).

 

[12Keys]

Even if it was a template, how would you stop people from saving over
it?

If its sitting on a network drive, and 50 people need to take a copy of
it. there is always 1 person thats going to get it and save over it?
how do I make sure they don't?

 

[Zack Barresse]

Maybe you could use a BeforeSave event then...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim strFileName As Variant, strFilter As String
strFilter = "Excel Files (*.xls), *.xls"
If LCase(Me.Name) = "master.xls" Then
Cancel = True
strFileName = Application.GetSaveAsFilename("Default Name.xls",
strFilter)
If TypeName(strFileName) = "Boolean" Then
MsgBox "You pressed cancel!", vbInformation
Else
If Right(LCase(strFileName), Len(strFileName) -
InStrRev(strFileName, "\")) <> "master.xls" Then
MsgBox "You chose to save:" & vbNewLine & strFileName,
vbInformation
' Application.EnableEvents = False
' Me.SaveAs strFileName
' Application.EnableEvents = True
Else
MsgBox "I said DO NOT save as ""Master.xls!"""
End If
End If
End If
End Sub

Uncomment the three commented lines to save. Easy to test with the save
lines commented out.

HTH

 

[Tom Ogilvy]

Just a thought,
Most users are offered the opportunity to disable macros which totally
defeats the solution specified by the OP.

It might be useful to utilize the file protection capabilities of the
operating system.