Password protect sheet to prevent viewing, while allowing copying data to those sheets to work

[Lux Aeterna]

Hello there!

We use a workbook that has some sheets which I need to be password-protected from viewing. My main concern is that because my colleagues are not very adept at using computers, they might accidentally delete data.

I know I can lock cells and sheets, but that wouldn't be of much help, as I have to edit those sheets regularly, and I might forget to lock them back. I also know I can hide them, but that wouldn't be of much help either, as some of my colleagues need to have access too. So, I thought it would be better to ask for a password each time someone tries to view that sheet.

The sheet names I need to be password-protected are all named List**** (e.g., List2022, List2023, etc.). Please note that there are macros that copy data from other sheets to the List**** sheets. It might be necessary to allow macro-induced commands.

Thank you in advance!

 

[Logit]

they might accidentally delete data.

Prior to closing the workbook ask them if they want to save the changes they made.

and I might forget to lock them back.

Auto lock the workbook prior to closing it.

as some of my colleagues need to have access too.

One of the short comings to having a need for passwords.

Also, include a macro to auto close the workbook after a specified period of non-activity. Do so without saving any changes and apply all the required passwords.

 

[Micron]

It sounds like those that need to see the sheets are trusted while the others not so much. So one way might be that when the wb opens, use vba to show the sheets to those who might need to edit them (unhide) but don't for the others. You could hard code the names or use a hidden list. The former would be more secure if you password protect the vba project but forgetting the pw would be a problem. A list of names could be exposed by someone but may not be much of an issue for you. When the wb closes, code hides the sheets again.

 

[Lux Aeterna]

Thank you for your answers

@Logit,
Workbook is auto-saved every time they perform an action. That's necessary, because they used to close WB without saving changes. So, closing the WB without saving, while sounds good, is not an option. Therefore, I guess the rest of your suggestions cannot be used.

@Micron,
Not really sure what hardcode is, but I can search for it. I am not sure that "trusted" colleagues will be able to use the VBA manually though.

So, I was thinking that a macro that asks for a password to view the List* sheets would be an easy to use option.

 

[Micron]

Hard coded means that the values code needs to work with are written in the code itself - they are not looked up in some sheet (or in the case of databases, tables). So to use that method you'd write the names in code. While it can prevent users from editing lists that they might find, it requires the codes to be edited if the list of users changes.

I am not sure that "trusted" colleagues will be able to use the VBA manually though.

It seems I didn't state that clearly enough as there would be nothing for them to do. The simple act of opening and closing the workbook would take care of it. If you want to allow or disallow prompts to save changes, that can also be included.

 

[Peter_SSs]

I don't have time right now to look at the specific modifications for you, but you might be able to get something useful from this old thread.

Password Protecting Individual Sheets

I would like to adjust the code found in the attached thread so that i can allow one person access to more than 1 sheet http://www.mrexcel.com/forum/showthread.php?t=427756&page=2 any help would be much appreciated! THANKS! :biggrin:

www.mrexcel.com