Searching through Mr Excel for my issue I found this old thread which is perfect, just what I need. However, there must be something missing in the code that I have copied from it. With the code below in ThisWorkbook, I click sheet 4, it disappears from view, and I get prompted for the password. I enter TEST in the box and it just asks me for the password again. It doesnt activate the sheet and end the macro. Can anyone point out where ive gone wrong please?
Dim LastActiveSheet As Worksheet
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Not Sh Is Sheets("Sheet4") Then
Application.ScreenUpdating = False
Set LastActiveSheet = Sh
End If
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh Is Sheets("Sheet4") Then
Sh.Visible = False
With Application
EnableEvents = False
LastActiveSheet.Activate
EnableEvents = True
End With
PromptForPassword
End If
End Sub
Sub PromptForPassword()
Dim UserInput As Variant
Const PWord1 As String = "TEST"
Const Msg1 As String = "Sheet Locked For Viewing !" & vbNewLine _
& vbNewLine & "Enter Password To Unlock."
Const Msg2 As String = "Wrong Password !"
With Application
Do
UserInput = .InputBox(Msg1)
Select Case UserInput
Case Is = False ' if user cancells don't activate sheet
Exit Do
Case Is = PWord1 '**if password correct activate sheet4
Set LastActiveSheet = Sheets("Sheet4")
Exit Do
Case Else 'if wrong password give user another try
UserDecision = MsgBox(Msg2, vbRetryCancel): Beep
End Select
Loop Until UserDecision = vbCancel
Sheets("Sheet4").Visible = True
EnableEvents = False
LastActiveSheet.Activate
EnableEvents = True
End With
End Sub