Excel 97 proctection

[Kurtie]

Hello there,

I have maybe a stupid question.
Problem:
only one person must be able to change the excel sheet.
other people must only look at it.
When a person opens the excel sheet the other one cannot change the sheet. OK that's good, but what if this other person is the one who must be able to change it ?

anyone got any idea's ? Kurtie

 

[oaliev]

Then you must change it

 

[BradSQLGuy]

Excel has software built in that when someone else has the spreadsheet open it will tell you if you are trying to save. The owner of the document is able to see the changes made and who actually made the changes. It will mark the changes with initials of that person...or whoever is registered for that PC (windows registration). Excel will save both copies and the owner can accept or reject what they want to keep. -Does that help ya?

 

[Kurtie]

Hello,

Thanks for the reply.
I've looked and found a solution. In the file-save as- options, you can specify the way other users can open the file. Password protected, read only,.. . Every time a user tries to open the file a question is asked. Or you give the password or you can open with read-only property.
The user who knows the password can also change the sheet.
This solves my problem. Kurtie

 

[BradSQLGuy]

You can also track changes incase someone else has the document open and two people are making entries at the same time. -Does that help ya?

 

[DaleWatson123321]

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 = &quot;Enter Password&quot;
Title = &quot;Password Required to Make Changes&quot;
Default = &quot;&quot;
passwd = InputBox(Message, Title, Default)
If passwd <> &quot;654123&quot; Then Exit Sub
ActiveSheet.Unprotect &quot;654123&quot;
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 &quot;123&quot;
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

 

[Kurtie]

Hi Dale,

Thanks for the help. And yes there is always someone
that can benefit.

The solution i've mentioned is sufficient for the moment
but if the users will complain I have now another solution
Kurtie