Hi Gary,

Not able to reply to the email that you sent. So asking here.

Thank you for replying.

I have no other option but to trust the integrity.

So, I used your below code to create a test file and I kind of thought it as best option instead of manually copy pasting results in email or providing hard copies.

However, i am not able to close the file as it gives me error message.

Step 1: Input Username or name of Worksheet - Name of Student.

Step 2- Input Unique password for each worksheet which is each password for each student

Step 3- Result. According to username password it will open respective sheet only.

Step 4- Close the workbook. Here I am facing an error without saving anything.


It Highlights this part of the code.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True



I need your help with this code. I think the code is working well after the error will be rectified just that in the code I am not able to set unique password for unique username(worksheet name). I can only set a single password for all username using this code.

Also, how can I have a blanket access to add marks of students if that is not possible then I will add one raw data worksheet and put in formulaes in students worksheet to vlookup the results from raw data worksheet.. Does that sound good?
Again, thank you for helping me out on the code. I really appreciate this.



On Monday, July 28, 2008 at 3:16:58 PM UTC+5:30, macropod wrote:
Hi Freshman,

Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module.

Option Explicit
Dim User As String
Dim UPwd As String
Dim WPwd As String
Dim Err As Boolean
Dim wsSheet As Worksheet
Dim wsActvSht As Worksheet

Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = wsActvSht.Name Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetVeryHidden
End If
End With
Next wsSheet
Restart:
User = InputBox("Please Input your Workbook Username")
UPwd = InputBox("Please Input your Workbook Password")
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then
On Error GoTo Restart
If .ProtectContents = True Then .Unprotect UPwd
.Visible = xlSheetVisible
.Activate
Exit Sub
End If
End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name = User Then .Protect UPwd
If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden
End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True
End Sub

The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their
worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the
prompt is repeated. If the prompts are left empty, the code does nothing more and exits.

The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code.
This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc.

You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll
leave that to you.

--
Cheers
macropod
[MVP - Microsoft Word]