how to disable saving???

  • Thread starter Thread starter willroy
  • Start date Start date
W

willroy

Hi,

In my code I want to temporarily disable the user to be able to sav
the spreadsheet.

The reason for this is that in my macro, I have created a manual filte
which uses the actual file of data so saves cannot be made over that a
I will be in deep ****!

I do not want to disable it totally as I have a msgbox asking to sav
and if yes, it save automatically with programmed name in a programme
folder.

PLEASE HELP!!!

Thank you

Wil
 
Will this "master" file always have the same name. Then you can check this
in the BeforeSave event in the thisworkbook module (see Chip Pearson's site
if you are not familiar with events:
http://www.cpearson.com/excel/events.htm )

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
if Thisworkbook.Name = "MyMasterBook.xls" then
msgbox "Can not save with this name - do saveas with a new name"
Cancel = True
End if
End Sub
 
Hi Tom

Thats very handy to know, thanks.

However, I am running a macro which opens the desired user's workbook,
autofilters data by specific dates, renames the worksheet, so I need to
disable anyone from saving the adjusted file after the macro has
performed.

I know I could prigram the workbook to close afterwards, but want to be
able to leave it open for the user to view.

I hope you can help.

Thanks

Will
 
Willroy said:
In my code I want to temporarily disable the user to be able to save
the spreadsheet.
Click to expand...

Sounds like a job for a Workbook_BeforeSave event procedure in the
ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Something Then
MsgBox "You can't save this now"
Cancel = True
Exit Sub
End If
' do other things as required before saving
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
You can still put that code in the workbook you describe (the one you don't
want saved). If your code needs to save it with its original name, you can
just do

Application.enableEvents
workbooks("Data.xls").Save
Application.enableEvents

If you want to close it without saving and don't want the prompt

Workbooks("Data.xls").Close Savechanges:=False

Not sure why you think that BeforeSave is not a workable approach, even
given the scenario you describe.

But for diversity, you can also change the file's access mode to readonly

workbooks("data.xls").ChangeFileAccess Mode:=xlReadOnly
 
Back
Top