Hi,

I am looking for a Macro to run so that users cant save as or create a copy
of a file in excel, I only want the user to use the save icon or when you
press the red cross for a popup to appear saying only save new changes in an
excel workbook.

The people using this workbook are creating copies by accident and so are
working from different files all the time, they dont know how to disable
macros and they dont have reason to I am looking for this code to prevent
creating copies of filename.

Can anyone please help me with this.

Many thanks

Terilad

Look at the BeforeSave event.
You could use something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
If Not SaveAsUI Then
Cancel = False
MsgBox "Saved normally."
Else
MsgBox "You tried to do a Save as ... naughty boy!", vbCritical, "File Not
Saved!"
End If
End Sub

Of course the message box is mainly for testing, edit it accordingly.
Place the code into the ThisWorkbook code module, not a normal module.

This wont stop determined users, who may copy individual worksheets to new
or existing workbooks. Or may use code like, Sheets.Copy. Not sure how one
could stop that. :)


Regards
Robert McCurdy

"terilad" wrote in message
...
Hi,

I am looking for a Macro to run so that users cant save as or create a
copy
of a file in excel, I only want the user to use the save icon or when you
press the red cross for a popup to appear saying only save new changes in
an
excel workbook.

The people using this workbook are creating copies by accident and so are
working from different files all the time, they dont know how to disable
macros and they dont have reason to I am looking for this code to prevent
creating copies of filename.

Can anyone please help me with this.

Many thanks

Terilad

Hi, I have tried the code but cant seem to get it to operate, compile errors
etc, I copied the code from here and paste, possibly the reason, how should
the code look in VBA so it can operate?

Regards and thanks.

Terilad

"Robert McCurdy" wrote:

Look at the BeforeSave event.
You could use something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
If Not SaveAsUI Then
Cancel = False
MsgBox "Saved normally."
Else
MsgBox "You tried to do a Save as ... naughty boy!", vbCritical, "File Not
Saved!"
End If
End Sub

Of course the message box is mainly for testing, edit it accordingly.
Place the code into the ThisWorkbook code module, not a normal module.

This wont stop determined users, who may copy individual worksheets to new
or existing workbooks. Or may use code like, Sheets.Copy. Not sure how one
could stop that. :)


Regards
Robert McCurdy

"terilad" wrote in message
...
Hi,

I am looking for a Macro to run so that users cant save as or create a
copy
of a file in excel, I only want the user to use the save icon or when you
press the red cross for a popup to appear saying only save new changes in
an
excel workbook.

The people using this workbook are creating copies by accident and so are
working from different files all the time, they dont know how to disable
macros and they dont have reason to I am looking for this code to prevent
creating copies of filename.

Can anyone please help me with this.

Many thanks

Terilad

Ok I fixed it,

Many thanks for your help.

Terilad

"Robert McCurdy" wrote:

Look at the BeforeSave event.
You could use something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
If Not SaveAsUI Then
Cancel = False
MsgBox "Saved normally."
Else
MsgBox "You tried to do a Save as ... naughty boy!", vbCritical, "File Not
Saved!"
End If
End Sub

Of course the message box is mainly for testing, edit it accordingly.
Place the code into the ThisWorkbook code module, not a normal module.

This wont stop determined users, who may copy individual worksheets to new
or existing workbooks. Or may use code like, Sheets.Copy. Not sure how one
could stop that. :)


Regards
Robert McCurdy

"terilad" wrote in message
...
Hi,

I am looking for a Macro to run so that users cant save as or create a
copy
of a file in excel, I only want the user to use the save icon or when you
press the red cross for a popup to appear saying only save new changes in
an
excel workbook.

The people using this workbook are creating copies by accident and so are
working from different files all the time, they dont know how to disable
macros and they dont have reason to I am looking for this code to prevent
creating copies of filename.

Can anyone please help me with this.

Many thanks

Terilad