Prevent SaveAs on Protected Sheet

C

Chilired

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,
 
J

JP

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
 
C

Chilired

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,
 
J

JP

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
 
C

Chilired

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.
 
J

JP

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
 
C

Chilired

I am a newbie to macros; would you please give me the complete macro for
disable/enable hot keys and or buttons and menu options. I am picking this
up slowing, but this project is needed asap. If I decide to alter the end
user's experience how do I restore them during the Workbook_BeforeClose
event, so that they are available the next time they start Excel?

Thanks,
 
J

JP

I understand what you need, but if I just write all the code for you,
how will you ever be more than a newbie? I've provided several
pointers for starting code you can use, and some keywords you can use
to search.

Try to apply some of the code and concepts I've given you. For
example, the Worksheet_BeforeRightClick Event.

If you go to the VB Editor and go to the first sheet module, choose
"Worksheet" from the first dropdown box and "BeforeRightClick" from
the second dropdown (see "Getting Started With Events" on this page
for visual aid: http://www.cpearson.com/excel/Events.aspx).

When you select that event, the VBE writes the shell code for you.
You'll notice there is a Cancel argument:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)

That means you can cancel the right-click event. I've already given
you the code to do that: Cancel = True

So if you want to stop right-clicking on your worksheet, here is the
code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)
Cancel = True
End Sub

Keep in mind if the worksheet is already protected, this code might be
redundant. Also, since it's a Worksheet-level Event, you'll need to
repeat this for every worksheet in the workbook.

Anyway, is all this protection really necessary? Can't the end users
simply be trained to not do some of the actions you want to prevent?

--JP
 
C

Chilired

Thanks for the detailed reply. I'll try the concepts and see what happens.
The majority of the users are external; training not an option. As
mentioned, PDF is really the preferred tool, however PDF does not permit
sorting. Couldn't have gotten this far without your assitance, thanks.

Chilired
 
J

JP

If you trap the BeforePrint and BeforeSave events, that should be
sufficient. You may not really need to disable toolbar buttons. It's
just another tool in the arsenal you can use to make your workbooks
appear more professional. But it's not necessary and may be overkill,
depending on the situation.

--JP
 
C

Chilired

JP - I can't get the Private Sub Workbook_BeforeClose(Cancel As
Boolean)...ReadOnly to work? I cut/paste your code. What could I have
possibly done wrong?
 
C

Chilired

Yes, there are 4 modules plus the This Workbook, how can I tell if it is in
the ThisWork Module. FYI - I did not select read-only in Options, the
document is password protected and it automatically opens on the web as a
read-only document. Would any of this impact the procedure?
 
J

JP

You can tell if it is in that module by first double clicking the word
"ThisWorkbook" to make sure you are viewing it, before pasting in the
code. See http://www.rondebruin.nl/code.htm if you need assistance
locating this module or pasting in the code.

I really couldn't say what the outcome will be, due to the fact that
it is being viewed on an intranet and I am unfamiliar with the
particular conditions at your site. Test it out by opening the
workbook and carefully noting any startup messages you get from Excel
(i.e. "Open As Read only?" etc) and whether it says [Read Only] in the
Title Bar. Then make some changes and try to close the file. If
successful, the code should allow your workbook to close silently,
discarding the changes. Open the workbook again and see if your
changes were discarded. If so, congratulations! It works.

Otherwise, you can just change the code to prevent saving altogether.
I think that was your goal anyway.

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


--JP
 
C

Chilired

JP: I did as you instructed and it worked perfect.

Another question...once the document is open on the intranet the user
selects a link a dialog box appears stating Opening document.....do you trust
this source.....would you like to open this file, how may I change this to
always automatically chose yes for this workbook?

Aadditionally when the user clicks "Back" on the explorer menu to return to
the document, a message appears requesting the user to Open/Save/Cancel, it
is possible to make that go away?

Thanks, Ann

JP said:
You can tell if it is in that module by first double clicking the word
"ThisWorkbook" to make sure you are viewing it, before pasting in the
code. See http://www.rondebruin.nl/code.htm if you need assistance
locating this module or pasting in the code.

I really couldn't say what the outcome will be, due to the fact that
it is being viewed on an intranet and I am unfamiliar with the
particular conditions at your site. Test it out by opening the
workbook and carefully noting any startup messages you get from Excel
(i.e. "Open As Read only?" etc) and whether it says [Read Only] in the
Title Bar. Then make some changes and try to close the file. If
successful, the code should allow your workbook to close silently,
discarding the changes. Open the workbook again and see if your
changes were discarded. If so, congratulations! It works.

Otherwise, you can just change the code to prevent saving altogether.
I think that was your goal anyway.

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


--JP

Yes, there are 4 modules plus the This Workbook, how can I tell if it is in
the ThisWork Module. FYI - I did not select read-only in Options, the
document is password protected and it automatically opens on the web as a
read-only document. Would any of this impact the procedure?
Click to expand...
Click to expand...
 
J

JP

I don't think so. Not all dialog boxes can be dismissed
programmatically, and using Excel on an intranet is a different
environment than just using it on a network share or on your personal
computer. You might want to repost this latest question as a new topic
so others can respond.

--JP
 
Top