Vba protected worksheet - help!

[stevembe]

In need of assistance, quite quickly if possible as I have goofed! The task was to password protect a single worksheet so that it prompted people for a password if they tried to view it. I checked the internet and found the following code:

Sub ToggleCompSheets()
Dim ConfirmHide As String
Application.ScreenUpdating = False
If Sheets("Sheet2").Visible = xlSheetVeryHidden Then
GetCompApproval
Sheets("Sheet2").Visible = xlSheetVisible
Sheets("Sheet2").Activate
Else
ConfirmHide = MsgBox(prompt:="This will hide the Sheet2 tab." & vbCr & vbCr & _
"You will need a password to access " & vbCr & _
"the tab if you choose to continue." & vbCr & vbCr & _
"Do you wish to continue?", Buttons:=vbYesNo, _
Title:="Hide Sheet2 Tab")
If ConfirmHide = vbNo Then
End
Else
Sheets("Sheet2").Visible = xlSheetVeryHidden
Sheets("Index").Activate
'Range("c8").Select
End If
End If
End Sub

Sub GetCompApproval()
Dim Password As String
Dim RetryPass As String
Password = InputBox(prompt:="Enter password to display Sheet2 tab", Title:="Password")
'MsgBox prompt:=Password
If Password = "" Then
End
ElseIf Password <> "1966" Then
RetryPass = MsgBox(prompt:="Sorry, password is incorrect" & vbCr & _
"contact Steve on Ext XXXX" & vbCr & _
"if you need a password", _
Title:="Access Denied", _
Buttons:=vbRetryCancel + vbExclamation)
If RetryPass = vbRetry Then
GetCompApproval
Else
End
End If
End If
End Sub

The problem is whilst the code did hide the worksheet I can not open it again, even from the VB editor. By looking at the code I think the password is 1966 but if I type that in it just returns to the VB editor. Can anybody see the problem or indeed suggest an easier way to do this.

Many thanks in advance

 

[Ruddles]

Normally the discussion of breaking passwords is not permitted on this forum, however I think this falls outside that description. I trust the moderators will agree.

Looking at the code, the password does appear to be "1966".

If the only problem is hidden worksheets, go to the VBA Immediate window (Ctrl-G) and type (or paste) this:-

For Each ws In Worksheets: ws.Visible = xlSheetVisible: Next ws

and hit Enter.

Any hidden worksheets should reappear.

 

[Ruddles]

As a general guideline, when you're testing code which has the potential of locking or destroying your workbook, make frequent backups. That way you don't lose too much work when the inevitable happens!

 

[Steeviee]

Your internet traul missed a vital bit of code!

Sub GetCompApproval()
Dim Password As String
Dim RetryPass As String
Password = InputBox(prompt:="Enter password to display Sheet2 tab", Title:="Password")
'MsgBox prompt:=Password
If Password = "" Then
End
ElseIf Password <> "1966" Then
RetryPass = MsgBox(prompt:="Sorry, password is incorrect" & vbCr & _
"contact Steve on Ext XXXX" & vbCr & _
"if you need a password", _
Title:="Access Denied", _
Buttons:=vbRetryCancel + vbExclamation)
If RetryPass = vbRetry Then
GetCompApproval
Else
End
End If
[COLOR=red]Else[/COLOR]
[COLOR=red]Sheets("Sheet2").Visible = xlSheetVisible[/COLOR]
[COLOR=red]Sheets("Sheet2").Activate[/COLOR]
End If
End Sub

 

[stevembe]

Thank you both very much indeed, with your help my problem has been solved and everything works a treat now.

Many thanks

 

[Steeviee]

You're welcome. Thanks for the feedback.