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

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy


"willroy" wrote in message
...
Hi,

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

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

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

PLEASE HELP!!!

Thank you

Will


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

Willroy wrote:
In my code I want to temporarily disable the user to be able to save
the spreadsheet.


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

--
Regards,
Tom Ogilvy


"willroy" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/