Hi Kurtie,
If using the option you've described is acceptable in your situation, then that's GREAT.
However, as you might have noticed, there are a couple of "drawbacks" with using "file-save-as-options"...
All users who open the file are presented with the "Password" window every time they open the file, whereas with the following option, this window does NOT appear.
Also, users who do NOT have the password can still open the file as Read Only, AND can make changes, AND can save the file under a DIFFERENT filename. They THEN have an ENTIRE UNPROTECTED copy of the file.
In some cases the above might be fine. However, in other cases it might be preferable (or even REQUIRED) to have protection that PREVENTS a user from taking EITHER of the above actions. This IS possible, with the following steps...
1) For the user(s) you want to give permission to make changes, use code such as following. Of course you could attach it to a button. But perhaps a preferable option would be to create a keyboard shortcut such as <Control> <Shift> P - thus making it inconspicuous.
By inserting this code into a Module, it can be utilized for any additional Worksheets within the Workbook. But, for any “special” Worksheets that ONLY YOU want to access to, you can assign a DIFFERENT password. Also, YOUR password for the Workbook would be DIFFERENT and one that ONLY YOU would retain.
Sub Get_Password()
Dim Message, Title, Default, passwd
Message = "Enter Password"
Title = "Password Required to Make Changes"
Default = ""
passwd = InputBox(Message, Title, Default)
If passwd <> "654123" Then Exit Sub
ActiveSheet.Unprotect "654123"
End Sub
2) Use <Alt> <F11> to go into the Visual Basic Editor. Then Under Project Explorer (left side of window), click on “ThisWorkbook”. Then on the right side window, enter the following code...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Protect "123"
If Me.Saved = False Then Me.Save
End Sub
As you can see, this “BeforeClose event will ensure that whenever the Workbook is saved, it will automatically be password protected. Naturally choose your own passwords - here and in the other Get_Password routine.
3) While still in the Visual Basic Editor… from the menu, choose: Tools – VBAProject Properties. Then click the “Protection” tab. Check off “lock project for viewing”. Enter your password, confirm it, and click OK.
4) For each of the Worksheets that you want to protect, from Excel’s Main menu…. use: Tools – Protection – Protect Sheet – and of course assign the SAME password as you’ve used in your Get_Password routine.
5) To protect the Workbook, use: Tools – Protection – Protect Workbook. There are obvious advantages to protecting the Workbook. However, appreciate that the Workbook protection does NOT have to be activated in order for the VBA code Protection to be in effect. The VBA Protection is “separate” – But you will probably have to “save” the file first and re-open it before it becomes “active” – i.e. the user CANNOT gain access to the VBA code (without the password).
One Last Note: While this type of protection might be a “better option”, there STILL is a “loophole”. If a user wanted to copy the entire contents of a file protected in the above manner, the user only has to open a blank workbook, and then copy entire sheets – one-at-a-time – to sheets in the blank workbook. The ONLY thing missing would be the VBA code.
“There I go again”... getting “carried away” with my explanations. But I hope someone out there can benefit from the above.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
