Quote:
Originally Posted by Judy P.
I'm sorry, I forgot to mention that I am oblivious to Visual Basic code. Everything I had done so far is just by copy or trial and error. I am not sure how to incorporate the protection in the code. Will I need to insert the "unprotect" and "protect" before and after each event? Can I still use a password? Will it still work the same way that it does right now?
The worksheet that this is for, contains salary information for each country/office. This information will be linked/referenced in another worksheet within the same workbook. I would like to have two levels of protection, one that allows only managers to access the salary worksheet, the other protection is to prevent managers from editing the timestamp cells. I hope that helps make my intentions clearer.
Thank you so much for your reply. I don't know where else I can find this information. You're my hero.
|
Hello,
You only have to run this short macro once to protect your active worksheet and workbook.
Your macro ( Private Sub Worksheet_Change(ByVal Target As Range) ) should work even if
worksheet is protected. This is because protection concern only users not VBA-code, therefore
code can change values at C4:F4.
Perhaps you should try following advice with a copy of your original file.
It worked okei, with my worksheet which i made to test your macro. It's fine too.
Try these steps :
1 ) Open the file you are goin to protect
2 ) Press ALT + F11
3) If you dont have any modules at your Project, insert one
by selecting INSERT - MODULE
4 ) paste the following code at empty module window ( you can add
one module even if there already are one or more modules
at your workbook )
Sub ProtectedByPassWord()
ActiveSheet.Protect UserInterfaceOnly:=True, Password:="SH", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Password:=("WB"), Structu=True, Windows:=False
End Sub
5) save the code ( press CTRL + S or save by selecting FILE - SAVE )
6) Switch to normal Excel wiev
7 ) Press ALT + F8 to see macros that you are able to run
8) find ProtectedByPassWord, select it and press run
9) the previous action protected your active sheet with password "SH" and workbook
with password "WB"
10 ) Now you can test if the worksheet and workbook works as before protection
You can unprotect the sheet and workbook normally manually but the passwords
are those used in ProtectedByPassWord - macro.
Ofcourse you can put your own passwords instead of these easy ones. Place them
between the quotation marks, exactly where SH and WB is now.
If you try this macro first with my passwords, you have to manually remove protection
from worksheet and workbook before you can run ProtectedByPassWord - macro
with your own passwords.
*********
ATTENTION :
Be careful with these passwords, anyone who can see your ProtectedByPassWord - macro
can easily unprotect your workbook and worksheet. To prevent that try to run
this macro from another workbook. You may have to refer to the workbook you want to
protect by name then.
*********