hide/unhide worksheets (Advanced VBA Code)

Welcome to the forum.

Which version of Excel do you have? Please update your forum profile.

Originally Posted by t.folsom80

Hello Everyone,
I'M looking for help to create VBA code that will require a password to toggle a cell
example:
sheet 1 cell Q1 is a drop down box with Open or Closed to toggle this function I would like to use a password in this cell to be able to select an option.
If sheet 1 = open, password required to unhide sheet 2
if sheet 1 = closed , no password required to hide sheet 2

To be clear ... you want:
1. the user to enter a password in sheet1 cell Q1 in order to be able to change the value in that same cell, and
2. if the user subsequently sets the value to open then no password is required to activate sheet 2 but if it is set to closed then password required.

Is that right?

There are two issues with what you're trying to do:

1. Entering a password that is not in the list of acceptable values is problematic. You wouldn't ask for the password to change the selection until after the user had attempted to do so, which means entering a password in the same cell. This can be overcome but I would suggest using an input box to request the password; if it's correct the value stays as it is and if it's wrong it can be reverted to the previous value.

2. Excel doesn't have a BeforeActivate or BeforeDeactivate event for worksheets. This means if you use the Activate event of Sheet2 as the trigger to check the sheet access password has been set, there will be a split second where the user can see what is in Sheet2 before being switched back to Sheet1 if it is incorrect. It would be smoother and more secure if Sheet2 was simply hidden from users until the password for sheet access was set.

What do you think?

Also, in case you're not aware, this sort of security functionality will keep normal users out but a determined user trying to get around the security can do so if they have a little technical ability.

Thinking about this some more, I don't think you need a sheet password. If the user sets Q1 to Open then Sheet2 is visible and they can select it; if Q1 is Closed then Sheet2 is hidden and they can't. As mentioned, if you use the Activate event to test whether a password is required or not, the user will momentarily see Sheet2.

Something like this - put this in the worksheet module for Sheet1:

Please Login or Register  to view this content.

Put this in the workbook module:

Please Login or Register  to view this content.

And put this in a normal module:

Please Login or Register  to view this content.

Hopefully I am understanding what you want.
If the user selects Open from the Q1 drop down they will be asked for a password, if it matches then sheet2 will be visible.
If they choose Close they won't be asked for a password and sheet2 will not be visible.

Please Login or Register  to view this content.