Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Google Doc Spreadsheet with row protection...

    I have a friend who teaches in a local college. He wants to create an S by S matrix in a Google Docs spreadsheet.
    S by S is students by students. So, with 20 students in his class, he wants a 20 x 20 array.

    What the students are supposed to do is rate presentations given by other students, but not change, erase, etc. other ratings.
    So, essentially, student 1 can rate all 19 others, but can't tamper with the ratings any of the other 19 give to each other.
    Ideally, it's protecting a ROW with a unique password for each student...which I don't think is possible.

    Anyone have any ideas on this?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    KW,

    This sample workbook with 5 students will open to a splash screen with a credentials dialogue box requesting that the user enter his/her username and password. A validation process indicates if the credentials are valid or not. If they are, the splash screen closes and the grid screen opens. Based on the user that is logged in, only one column is available to the user and is locked out from the rest of the matrix. The user still has access to the rest of the workbook however.

    Cell A1 indicates the user currently logged in and there is a button to log out. When the user logs out, the log out button becomes a log in button which will open the credentials dialogue window when clicked. If a new user logs in, A1 will display their name and their cell permissions will be adjusted accordingly.

    The VBProject should be locked with a password to prevent a user from discovering the passwords. I would normally lock it, however, Zeddy has know-how to unlock it anyway.

    The valid usernames/passwords are:

    Student1/Password1
    Student2/Password2
    Student3/Password3
    Student4/Password4
    Student5/Password5

    but they can be easily changed in the code. I set it up so that a user has access to only one column but it could be adjusted to restricting rows instead. I could also make an adjustment if the user does not have access permission to critique him or her self. The workbook will automatically save on exiting

    HTH,
    Maud

    KW1.png

    KW2.png

    PS. Forgot to mention that the user cannot progress beyond the splash screen if macros are disabled or if the user attempts to side-step macros when opening by holding down the Shift key
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-27 at 21:27.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Clip0020.jpgI will check it out later this evening. THANKS!!

    Each student should have access to every column (the other students, except their own -- they can't rank themselves).

    So, Student1 should be able to enter a ranking or scoring value for all of the other students.

    I wonder if this will work in Google Docs so he can post it there and have all the students access it?!

    oops...got an error.

    oops #2: never mind. Saved it and reopened and it's fine.

    The student can enter down that student's column. But, any logged in student can trash (delete or change) student names in rows or columns. Need to restrict that.
    Last edited by kweaver; 2015-05-27 at 23:06.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    I set it up so that a user has access to only one column but it could be adjusted to restricting rows instead
    KW,

    Whereas in your perception where students are rating across, in my sample, students are rating down. Therefore, in your setup, each student has access to only one row, in my sample they have access to only one column. I will adjust the coding so that they rate across and they will not be able to rate themselves. I don't believe that this will work in Google Docs but it should on a share drive.

    My other thought is to have a web form that will feed the ratings onto a spreadsheet.

    Maud

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I think my friend can put this on a shared drive at the college. Look forward to testing it when you have time to adjust.

    I suspect I need to examine the code in the new version to see how to add more students and then lock the VB module.

    Thanks again. Always on top of things. You can tell you're a PA person. LOL.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    KW,

    I made the changes so that the student will rate across his row and not down a column. The student will receive a warning if they attempt to rate themselves. To make it easy to add/change students, I have made use of a hidden sheet called settings only accessible through the vb editor by changing visible property in the property window. Just change the usernames and passwords on Settings and they will be changes everywhere else and the code will adapt.

    HTH,
    Maud

    Grid sheet:
    KW5.png

    Settings sheet (very hidden):
    KW6.png
    Attached Files Attached Files

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    kweaver (2015-05-28)

  8. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    NICE. Thanks, again. I'll dig into this today.

    I can't seem to get to the Properties and see the hidden sheet.
    Last edited by kweaver; 2015-05-28 at 11:27.

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Alt-F11 > View (in the vb editor) > Properties Window (Make sure the Grid Sheet is selected).

    KW7.png

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    YEP, that's where I was, but still don't see the hidden sheet.

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Change the setting from xlSheetVeryHidden to xlSheetVisible. The sheet will then become visible in the Excel GUI. I had tested with the Grid sheet locked and there were no issues. If locked, the user will not be able to change the student us ER names.

    Maud

  12. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I must be really thick. I have unprotected the sheet, went in to VB and I still can't see the hidden sheet.

    Clip0026.jpg

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    KW,

    In your image, you have the property window for sheet 2. Go up to the Project window and select Sheet3 (Settings). The property window will display the properties for sheet3 (Settings). Now change the sheet to xlSheetVisible then look at Excel to see the Settings tab appear.

    If you still can't find it I'm booking a flight out to LaJolla, Ca. and it better be there when I get there!

  14. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    DUH. Thanks. You're all clear to book that flight, by the way. The sheet3 has been found!

    Always welcome for a visit. First BOTTLE of wine on me.

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    KW,

    Afterthoughts for your consideration:
    1. If not free text, there should probably be some data validation to restrict what the students could enter in the cells (rating system).
    2. There could also be a setting that will toggle the values as visible or hidden depending if you want students to be able to see the ratings other students gave. For example, the grid could be almost filled when student 7 logs in. He/she will see an empty grid except for their row as the enter the ratings.
    3. Tabulations on a very hidden sheet that can only be opened when a master Username/password is entered.

    HTH,
    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •