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]



Can you upload the file to an online place I can download it from so I can see
exactly what you are doing?

FWIW:
I have a StudentGradesManager addin that handles this nicely, but in a
different way; - 1 sheet per class, student records are filtered for viewing.
In your case of 1 sheet per student -OR- a separate 'report' sheet per query, a
secure solution should be fairly simple to implement if I know the structure of
your file.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion