Macro to run if unhide sheet option selected

This is close to what you're looking for, but the problem is if Sheet1 (The one that's hidden) is already visible, it asks for the Password again.

But maybe it does the trick for you. Put it in your Workbook module. By the way, change the "Sheet1" and "Mypassword" to suit your needs.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If ActiveSheet.Name = "Sheet1" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
If InputBox("Enter password to continue", "Password") <> "Mypassword" Then
Sheets("Sheet1").Visible = xlSheetHidden
Sh.Activate
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

Juan Pablo

------------------

Additional To Juan's Code

I would suggest that the first sheet be the changing sheet and the rest of the sheets will flow with that one. On the other sheets simply go to the cell that needs tha same data and "= cell on first sheet" Under tools... options.... calculations..... you can set the sheet to calculate automatically or manually (F9). The size of the data or spreadsheet will help you determine which one you want to do.

Mark, can you Repost, yours got messed up

I sure can

Basically, to eliminate the problem of getting asked for the password even when "Sheet1" is visible add a condition to check if sheet1 is visible:

' Add Test to see if "Sheet1" is visible
If Sheets("Sheet1").Visible <> True then

End If

Mark, thought of that, doens't work...

because when you unhide Sheet1, the sheet deactivate event takes place AFTER Sheet1 has been unhidden, and therefor, it doesn't ask for the password, if it was or not visible... that's my "problem"...

Juan Pablo

-------------------

Re: Mark, thought of that, doens't work...

OK, I didn't test it. My bad.

Doesn't your code need sheet1 to be visible in order to make it the active sheet?

Also, shouldn't there be a line of code that will make sheet1 visible if the password is correct though?

Don't want to sound bitchy, sorry.

It looks like Douglas is going to use some VBA to activate the hidden sheet.

So here is my suggestion:

Public Sub AskForPassword()
' Declare Variable
Dim HiddenSht As Worksheet
' Initialise variable - Can be whatever sheet you want it to be
Set HiddenSht = Worksheets("Sheet1")
' Call password routine
getPassword HiddenSht

End Sub

Public Sub getPassword(ByRef HiddenSht As Worksheet)

Dim sht As Worksheet

Set sht = ActiveSheet

If sht Is HiddenSht = False Then
If InputBox("Enter password to continue", "Password") <> "Mypassword" Then
HiddenSht.Visible = False
MsgBox "Incorrect Password", vbCritical
sht.Activate
Else
HiddenSht.Visible = True
HiddenSht.Activate
End If
End If

End Sub

because when you unhide Sheet1, the sheet deactivate event takes place AFTER Sheet1 has been unhidden, and therefor, it doesn't ask for the password, if it was or not visible... that's my "problem"... - : Basically, to eliminate the problem of getting asked for the password even when "Sheet1" is visible add a condition to check if sheet1 is visible

Re: Mark, thought of that, doens't work...

As i understood the problem, he wants to ask for the password when the user goes to Format - Sheet - Show... and then, if he selects Sheet1, the Deactivate event triggers, and also does just by switching sheets...

If it is directly through VBA then ok, you gave the code.... if it isn't, i don't know what to do...

Juan Pablo

--------------- OK, I didn't test it. My bad. Set sht = ActiveSheet If sht Is HiddenSht = False Then If InputBox("Enter password to continue", "Password") <> "Mypassword" Then HiddenSht.Visible = False MsgBox "Incorrect Password", vbCritical sht.Activate Else HiddenSht.Visible = True HiddenSht.Activate End If End If

Re: Mark, thought of that, doens't work...

good point. Perhaps we should ask Douglas what he wants specifically. :) As i understood the problem, he wants to ask for the password when the user goes to Format - Sheet - Show... and then, if he selects Sheet1, the Deactivate event triggers, and also does just by switching sheets... If it is directly through VBA then ok, you gave the code.... if it isn't, i don't know what to do... --------------- : OK, I didn't test it. My bad. : Set sht = ActiveSheet : If sht Is HiddenSht = False Then If InputBox("Enter password to continue", "Password") <> "Mypassword" Then HiddenSht.Visible = False MsgBox "Incorrect Password", vbCritical sht.Activate Else HiddenSht.Visible = True HiddenSht.Activate End If End If : End Sub

Re: Mark, thought of that, doens't work...

Wow - lots of feedback - thanks guys.

What I wanted was to protect the sheet so that it couldn't be unhidden by someone selecting Tools-sheet-unhide without entering a password.

I got round the problem by adding two buttons to my spreadsheet. On hide the sheet using sheet.visible - xlveryhidden which means that it doesn't appear on the list of hidden sheets when you use the tools-sheet-unhide menu.

The other button opens an input box asking for a password - if the password is correct the sheet gets unhidden!

I'm sure it's a bit long winded but it works
Cheers for your help though
Douglas

good point. Perhaps we should ask Douglas what he wants specifically. :) : As i understood the problem, he wants to ask for the password when the user goes to Format - Sheet - Show... and then, if he selects Sheet1, the Deactivate event triggers, and also does just by switching sheets... : If it is directly through VBA then ok, you gave the code.... if it isn't, i don't know what to do... : ---------------