Protect Sheet at all times

[greekboyuk]

Hello,

I have created a workbook which one of the sheets is password protected.The main password allows everything to be edited and the other just 2 columns of the range. The file will be stored in a shared drive.


Problem:

I opened the spreadsheet, entered the master password to allow full editing and made certain changes. Then I save the spreadsheet. I reopened the spreadsheet from a different computer ( testing purposes) and I noticed that I could edit everything. I don't like these one to happen.


Achievement

If one of my members of the team (8 users) enter the main password for the workbook to do any changes or update and is saved then

a) As soon as the workbook is reopened by any users password protect it with the existing password at all times so a password will be required again for editing.

.


Many Thanks


ilias

[mojo249]

Ilias

I don't get what you're trying to do. Do you want to password protect the sheet with the main password whenever the file is closed? If so,

1) Open the workbook
2) Press Alt-F11 to open the Visual Basic Editor
3) In the window top left, double click ThisWorkbook
4) At the top of the main window on the right, there are 2 drop down lists. On the left, select 'Workbook' and on the right select 'Before Close'
5) In the 'Private Sub Workbook_BeforeClose(Cancel As Boolean)' section, paste the following code

Please Login or Register  to view this content.

'Change Sheet1 to the name of the sheet you want to protect and 'mainpassword' to....your main password.

Dion

[royUK]

Mojo

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Added this time

[royUK]

Are you sure that you re-protected the sheet before closing?

[greekboyuk]

Are you sure that you re-protected the sheet before closing?

Hi roy,

That is what I want to do. I know that if I re-protect it with a password will be fine. Sometimes my employees may forget to password protect it and if that happens other unauthorized users will be able to make any changes

Let say I opened my spreadsheet (which is protected) and I unprotected it.Then, made some changes saved it and forgot to password protect it..Now if another user opens the spreadsheet it will be unprotected as I forgot to password protect it before save.

Is it any way where I can password protect automatically the sheet with a password that I will have pre-set ???


Many thanks

[greekboyuk]

Ilias

I don't get what you're trying to do. Do you want to password protect the sheet with the main password whenever the file is closed? If so,

1) Open the workbook
2) Press Alt-F11 to open the Visual Basic Editor
3) In the window top left, double click ThisWorkbook
4) At the top of the main window on the right, there are 2 drop down lists. On the left, select 'Workbook' and on the right select 'Before Close'
5) In the 'Private Sub Workbook_BeforeClose(Cancel As Boolean)' section, paste the following code

Please Login or Register  to view this content.

'Change Sheet1 to the name of the sheet you want to protect and 'mainpassword' to....your main password.

Dion

Hi mojo,

Thanks for your help. It looks that your code protects it but if user has macro disable doesn't work. What can you do in this case???


Many thanks

[royUK]

To use sheet protection properly you should protect all cells that should not be edited by locking them then applying the sheet protection. You shouldn't have people un-protecting the sheet randomly.

There is no way to re-protect a sheet other than manually if macros aren't enabled.

The bottom line is that sheet protection can actually be removed in seconds by someone with the knowledge or software to do it.

[mojo249]

You could hide the sheet when the workbook is closed and show it when it's opened. If the user doesn't have macros enabled, the sheet won't be visible.

Please Login or Register  to view this content.