Summary of what I am wanting to achieve:
Workbook opens on a Sign-In worksheet - user enters their unique password and gets taken to their worksheet - if they navigate away from their worksheet they have to go back to the Sign-In worksheet - when they close the workbook it re-sets to outcome one (Workbook opens on a Sign-In worksheet) - and finally a super password that can enter every worksheet
The practical use: I am trying to set a regular fortnightly test for my team (15 people) that is simple to use and writes itself. My team members supply one question each and the answer to that question making up a 15 question test.
On the 15 worksheets (one for each team member) is 15 rows with 3 columns - the rows have the names of each team member in column 1.
In column 2 they can write their question and in column 3 write the answer to that question.
They can see the questions already set by the team members who have had earlier shifts than them - but obviously not the answers. I have done this by using (for example) =John!D3 to import the question from John's worksheet to the other team members worksheets.
I want access to all their worksheets (the second password of each worksheet) so that I can make sure the questions have relevance to our work, the answers they have given are correct and I can, when everybody has set their questions and answered the questions of everybody else, go through and mark them.
I'll have a results worksheet that shows each team member whether he got the other questions right or wrong and that shows all the questions again with their answers so that, if anybody disagrees with the answer, thinks their answer was better etc., it can become a discussion point to the good of us all (the whole object of this testing system) - our work can be very subjective - hence the possible disagreement about the answers.
What code I have found: I have spent weeks trolling the net - few codes addressed my need that nobody can even view the worksheets without the correct password and reset the system on closing the workbook.... but, here is one that did (however it doesn't close (re-hide) the worksheet if you navigate away from the worksheet with the workbook still open):
[VBA]Option Explicit
Option Compare Text
'Password to unhide sheets
Const pWord = "help"
Sub HideSheets()
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
Worksheets("John").Visible = xlSheetVeryHidden
End Sub
Sub ShowSheets()
'Prompt the user for a password and unhide the worksheet if correct
Select Case InputBox("Please enter the password to unhide the sheet", _
"Enter Password")
Case Is = pWord
With Worksheets("Jon")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
Case Else
MsgBox "Sorry, that password is incorrect!", _
vbCritical + vbOKOnly, "You are not authorized!"
End Select
End Sub
Option Explicit
Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False
'Hide confidential sheet at startup
Call HideSheets
'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("Sign-In Page")
.Activate
.Range("A1").Select
End With
'Restore screen updates
Application.ScreenUpdating = True
End Sub
[/VBA]
I even found one code that had the option of 2 different passwords To view it: (But it was for only one worksheet - not the 15 I need)
[VBA]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 = "Enter Password1 Here"
Const PWord2 As String = "Enter Password2 Here"
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, PWord2 '**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
[/VBA]
CAN YOU HELP?
So, I've found some of the elements of what I want to do - I just don't know how to combine them and what I need to add to achieve my final goal - which (to re-iterate) is:
1. The workbook opens on a sign-in worksheet.
2. From a menu of names on that sign-in worksheet a unique password takes them To their worksheet.
3. If they move To another worksheet they have To go back To the sign-in menu To get back To their worksheet.
4. Each worksheet has 2 password options (theirs And mine which I use To access every worksheet) - alternatively, it might be easier To have a 'super' password icon for me on the sign-in worksheet that turns off all other passwords while I'm working the workbook
5. When the workbook Is closed it re-sets To 1.
Thanks for reading this and hopefully see an easy way to cobble the code I've already found to a bit more for me.
Regards,
Jon.
Reply With Quote
Originally Posted by jbri
