A protected read-only file resides on our Intranet. The user opens, sorts
data, view file etc.; however upon closing Excel ask "Do you want to Save the
Changes you made to File Name". How may I stop this from happening?

Thanks,

You could use the BeforeClose Event to stop saves.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.ReadOnly Then Me.Saved = True
End Sub


--JP

On Sep 23, 11:56*am, Chilired
wrote:
A protected read-only file resides on our Intranet. *The user opens, sorts
data, view file etc.; however upon closing Excel ask "Do you want to Save the
Changes you made to File Name". *How may I stop this from happening? *

Thanks,

I am new to VB; how do I get this Event to work? Also, if someone other than
me opens the sheet with the password will the file?

Thanks,

"JP" wrote:

You could use the BeforeClose Event to stop saves.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.ReadOnly Then Me.Saved = True
End Sub


--JP

On Sep 23, 11:56 am, Chilired
wrote:
A protected read-only file resides on our Intranet. The user opens, sorts
data, view file etc.; however upon closing Excel ask "Do you want to Save the
Changes you made to File Name". How may I stop this from happening?

Thanks,

You have to open & unprotect the file first, so you can edit it. Then
press Alt-F11 to access the VB Editor. Paste the code above in the
ThisWorkbook module for that workbook. Save and close, re-protect the
file and test it out. Let me know if that works.


--JP



On Sep 24, 4:46*pm, Chilired
wrote:
I am new to VB; how do I get this Event to work? *Also, if someone other than
me opens the sheet with the password will the file? *

Thanks,



"JP" wrote:
You could use the BeforeClose Event to stop saves.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
* If Me.ReadOnly Then Me.Saved = True
End Sub

--JP

On Sep 23, 11:56 am, Chilired
wrote:
A protected read-only file resides on our Intranet. *The user opens, sorts
data, view file etc.; however upon closing Excel ask "Do you want to Save the
Changes you made to File Name". *How may I stop this from happening? *

Thanks,-

I get a compile error "Invalid use of Me keyword". My original request is
not clear; let me try again.

The file is password protected and used for reference purposes only; it will
reside on our Internet and Intranet site. Multiple individuals are
responsible for updating the file. PDF was the preferred tool; however
end-users need the Sort function. The problem occurs when the user proceeds
to close the file; Excel prompts "Do you want to Save the Changes you made to
€¦€�. Technically there were no changes made; other than data sort and I don't
want the user to save the file anyway. Is there a way to prevent this prompt
from occurring?

Additionally, can you provide instructions on how to disable the Save,
SaveAs, Copy and possibly the Print functions and how to enable them again?

Thanks for your assistance.

"JP" wrote:

You have to open & unprotect the file first, so you can edit it. Then
press Alt-F11 to access the VB Editor. Paste the code above in the
ThisWorkbook module for that workbook. Save and close, re-protect the
file and test it out. Let me know if that works.


--JP



On Sep 24, 4:46 pm, Chilired
wrote:
I am new to VB; how do I get this Event to work? Also, if someone other than
me opens the sheet with the password will the file?

Thanks,



"JP" wrote:
You could use the BeforeClose Event to stop saves.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.ReadOnly Then Me.Saved = True
End Sub

--JP

On Sep 23, 11:56 am, Chilired
wrote:
A protected read-only file resides on our Intranet. The user opens, sorts
data, view file etc.; however upon closing Excel ask "Do you want to Save the
Changes you made to File Name". How may I stop this from happening?

Thanks,-

Try it this way instead:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ReadOnly Then ThisWorkbook.Saved = True
End Sub


The Print and Save/SaveAs methods can be cancelled by setting Cancel
to True for their respective events.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Msgbox "You cannot print this workbook."
Cancel = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Msgbox "You cannot save this workbook."
Cancel = True
End Sub

You may want to go a step further, however, and do the following:

1- intercept right-click (Worksheet_BeforeRightClick Event)
2- disable and/or remove buttons & menu options (by setting Visible or
Enabled Property to False)
3- intercept hotkeys like F12 (Save As) which can be used to display a
message to the end user.

For example, if you wanted to intercept the F12 key:

Sub Workbook_Open
Application.Onkey "{F12}", "MyMacro"
End Sub

Sub MyMacro
MsgBox "Don't do that!"
End Sub

Every time someone presses the F12 key, they'll get that nice little
messagebox.

If you do decide to alter the end user's experience (for example by
removing buttons from a toolbar) that you restore them during the
Workbook_BeforeClose event, so that they are available the next time
they start Excel.


HTH,
--JP

On Sep 29, 1:16*pm, Chilired
wrote:
I get a compile error "Invalid use of Me keyword". *My original request is
not clear; let me try again. *

The file is password protected and used for reference purposes only; it will
reside on our Internet and Intranet site. *Multiple individuals are
responsible for updating the file. *PDF was the preferred tool; however
end-users need the Sort function. *The problem occurs when the user proceeds
to close the file; Excel prompts "Do you want to Save the Changes you made to
…”. *Technically there were no changes made; other than data sort and I don't
want the user to save the file anyway. *Is there a way to prevent this prompt
from occurring? *

Additionally, can you provide instructions on how to disable the Save,
SaveAs, Copy and possibly the Print functions and how to enable them again? *

Thanks for your assistance. *