Excel spreadsheet for group

I want to setup a spreadsheet for our band students with everyone's name on the first sheet.  When a person clicks on their name it will take them to their own spreadsheet that is passworded so that only they can see their account.  Anyone have any ideas as to whether this can be done and how?
LVL 1
lrollinsIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Yes, this is possible.

However, a warning:

I am assuming that the workbook will be distributed to many parties, rather than being stored in a central repository (such as online in Google Docs, or a similar facility).

Once a single workbook has been distributed to many parties, each with the same set of passwords storage within it* (so that a comparison against an entered password & the correct password can be achieved), changing one of those passwords (such as in the event of accidental disclosure of a password to another party) will then mean that you need to redistribute a new workbook (with the revised password list) to each of the previous recipients.

* Note: The passwords within the workbook would themselves be protected by a single "master" (administrator) password.

The existing data is still very much at risk even after distributing an updated workbook. as previously distributed copies will still be in existence/use.

...

Have you considered simply creating multiple (bespoke) workbooks just with the individual recipient's single worksheet contents (rather than a single workbook containing everybody's worksheets), each password protected with a unique (workbook level) password for the recipient?

If a password has been disclosed, &/or needs to be changed (for whatever reason), then the risk of any other party viewing the contents is greatly reduced as only one copy of that workbook has ever been distributed.

...

Also be aware that even if the workbook is stored centrally, local copies can still be taken prior to a update being applied.

Additionally I am assuming that the passwords for each individual will be stored within the workbook rather than being maintained externally & stored within a central database (that is under your control) so that the individual distributed workbooks cannot be used unless a connection to that external repository is available at the time the workbook is opened.

BFN,

fp.
0
lrollinsIT ManagerAuthor Commented:
Sorry should have said that but yes it will be shared and stored in one location as you suggested.
0
[ fanpages ]IT Services ConsultantCommented:
Thanks.

Do you have an example workbook already prepared that can be posted here so that proposed solutions can be applied to it, or do you wish any contributing "Expert" to provide a fully-working prototype that you will need to (retro-)fit to your own workbook?
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

lrollinsIT ManagerAuthor Commented:
Here is a basic example.  The only thing I have done is hyperlink the student name to his worksheet.
Student-Account-Test.xlsx
0
[ fanpages ]IT Services ConsultantCommented:
Thank you for your sample workbook.

This project kept me busy for a while this morning! :)

The code below I have taken from the attached workbook.

The resultant workbook will have this code protected by a password but here I have not set a password so that it is easier for you (during review/testing of this proposal) to change/add (&/or delete) passwords for the individual Student worksheets.

We can discuss the adding of a password later & I can also instruct you on how to maintain the passwords for each worksheet in further comments within this question thread (if you cannot discover this for yourself).

However, for the time being, please look at the attachment & see if it meets your requirements.

I have retained your three sample worksheets; [Student List], [Student 1], & [Student 2].

I have also added a further eight sample worksheets for Students 3 to 10 (inclusive).  These are blank with the exception of cell [A1] that simply states the name of the worksheet.

The passwords for each of the worksheets is "S" followed by the respective number reference.

"S1" for [Student 1], "S2" for [Student 2], ... "S9" for [Student 9], & "S10" for [Student 10].

The case of the password (UPPER, lower, or MiXeD) is important.  The passwords are case sensitive; that is, "S1" is not the same as "s1".

Option Explicit
' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28234046.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28234046
' Question Title:   Excel spreadsheet for group
' Question Asker:   lrollins                                   [ http://www.experts-exchange.com/M_135006.html ]
' Question Dated:   2013-09-09 at 02:23:29
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

Private strSelected                                     As String

Private Const strCODENAME_PASSWORDS                     As String = "shtPasswords"
Private Const strCODENAME_STUDENT_LIST                  As String = "shtStudent_List"
Private Const strWORKSHEET_PASSWORDS                    As String = "Passwords"
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  Dim objWorksheet                                      As Worksheet

  On Error Resume Next
  
  For Each objWorksheet In ThisWorkbook.Worksheets
  
      If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST Then
         objWorksheet.Visible = xlSheetVeryHidden
      End If ' If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST Then
  
  Next objWorksheet
  
  strSelected = ""
  
End Sub
Private Sub Workbook_Open()

  Dim objWorksheet                                      As Worksheet

  On Error Resume Next
  
  For Each objWorksheet In ThisWorkbook.Worksheets
      
      If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST Then
         objWorksheet.Visible = xlSheetVeryHidden
      End If ' If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST Then
  
  Next objWorksheet
  
  strSelected = ""

End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

  Dim objWorksheet                                      As Worksheet
  
  On Error GoTo Err_Workbook_SheetActivate
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  For Each objWorksheet In ThisWorkbook.Worksheets
  
      If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST And _
         objWorksheet.CodeName <> strCODENAME_PASSWORDS Then
         If objWorksheet.Name = strSelected Then
            objWorksheet.Visible = xlSheetVisible
         Else
            objWorksheet.Visible = xlSheetVeryHidden
         End If ' If objWorksheet.Name = strSelected Then
      End If ' If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST And objWorksheet.CodeName <> strCODENAME_PASSWORDS Then
      
  Next objWorksheet
  
Exit_Workbook_SheetActivate:

  On Error Resume Next
  
  If Worksheets(strWORKSHEET_PASSWORDS).CodeName = strCODENAME_PASSWORDS And _
     Worksheets(strWORKSHEET_PASSWORDS).Visible = xlSheetVisible Then
     Sh.Visible = xlSheetVisible
     Sh.Select
  End If ' If Worksheets(strWORKSHEET_PASSWORDS).CodeName = strCODENAME_PASSWORDS And Worksheets(strWORKSHEET_PASSWORDS).Visible = xlSheetVisible Then

  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
  Exit Sub
  
Err_Workbook_SheetActivate:

  On Error Resume Next
  
  Resume Exit_Workbook_SheetActivate
  
End Sub
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

  Dim objCell                                           As Range
  Dim objWorksheet                                      As Worksheet
  Dim strPassword                                       As String
  
  On Error GoTo Err_Workbook_SheetFollowHyperlink
  
  Application.ScreenUpdating = False
  
  For Each objWorksheet In ThisWorkbook.Worksheets
      
      If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST Then
         If objWorksheet.Name <> Target.Name Then
            objWorksheet.Visible = xlSheetVeryHidden
         End If ' If objWorksheet.Name <> Target.Name Then
      Else
         objWorksheet.Visible = xlSheetVisible
      End If ' If objWorksheet.CodeName <> strCODENAME_STUDENT_LIST Then
  
  Next objWorksheet
  
  Application.ScreenUpdating = True
  
  Select Case (True)
  
      Case (Len(Trim$(Target.SubAddress)) = 0)
      Case (Len(Trim$(Range(Target.SubAddress).Address)) = 0)
      Case (Range(Target.SubAddress).Parent.Name <> Target.Name)
      
      Case (Worksheets(Target.Name).CodeName <> strCODENAME_STUDENT_LIST)
          If Worksheets(Target.Name).Visible = xlSheetVisible Then
             Worksheets(Target.Name).Select
          Else
             Application.StatusBar = "Password required for Student: " & Target.Name
     
             strPassword = InputBox(Target.Name, "Enter Password")
                            
             Set objCell = Worksheets("Passwords").[A:A].Find(What:=Target.Name, _
                                                              After:=[A2], _
                                                              LookAt:=xlWhole, _
                                                              MatchCase:=False, _
                                                              SearchDirection:=xlNext)
     
             If Not (objCell Is Nothing) Then
                If objCell.Offset(, 1) = strPassword Then
                   strSelected = Target.Name
           
                   Worksheets(Target.Name).Visible = xlSheetVisible
                   Worksheets(Target.Name).Select
                End If ' If objCell.Offset(, 1) = strPassword Then
             End If ' If Not (objCell Is Nothing) Then
          End If ' If Worksheets(Target.Name).Visible = xlSheetVisible Then
  
      Case Else
      
  End Select ' Select Case (True)
  
Exit_Workbook_SheetFollowHyperlink:

  On Error Resume Next
  
  Set objCell = Nothing
  
  Application.StatusBar = False
  Application.ScreenUpdating = True
  
  Exit Sub
  
Err_Workbook_SheetFollowHyperlink:

  On Error Resume Next
  
  Resume Exit_Workbook_SheetFollowHyperlink

End Sub

Open in new window


Thanks for providing your feedback when you are able to.

BFN,

fp.
Q-28234046.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lrollinsIT ManagerAuthor Commented:
Sorry it's taken me so long to reply but things have been very busy here.

Thanks for the worksheet it works great.  I'm going to do some testing and see if I need anything else done.
0
[ fanpages ]IT Services ConsultantCommented:
OK, not a problem.

Thanks for letting me know.
0
lrollinsIT ManagerAuthor Commented:
Just wanted you to know that the worksheet works great.  Now my only problem is finding a way to get it protected so no changes can be made by anyone except me and figure out how to make it accessible to them over the internet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.