Hi, I have a template (blank worksheet), I want to prevent the user to
override the template file. The code below will save the template as
different name (taken from cells). I don't know why everytime I press
save button, an error always comes up saying that '....The memory could
not be read' then it will close all the excel application. Any idea ? I
am using excel 2000. The code works fine if I saved it manually with
"file-save as". Thanks

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

Application.DisplayAlerts = False
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Dim Msg, Response
Msg = "File was saved as C:\Temp\" & Range("c10") & Range("c7") &
".xls"
Response = MsgBox(Msg, vbExclamation")

Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Try setting Cancel = true

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

Application.DisplayAlerts = False
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Dim Msg, Response
Msg = "File was saved as C:\Temp\" & _
Range("c10") & Range("c7") & ".xls"
Response = MsgBox(Msg, vbExclamation")
Cancel = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

"broogle" wrote in message
oups.com...
Hi, I have a template (blank worksheet), I want to prevent the user to
override the template file. The code below will save the template as
different name (taken from cells). I don't know why everytime I press
save button, an error always comes up saying that '....The memory could
not be read' then it will close all the excel application. Any idea ? I
am using excel 2000. The code works fine if I saved it manually with
"file-save as". Thanks

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

Application.DisplayAlerts = False
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Dim Msg, Response
Msg = "File was saved as C:\Temp\" & Range("c10") & Range("c7") &
".xls"
Response = MsgBox(Msg, vbExclamation")

Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub