How to allow a user to open a spreadsheet only once?

[Vijayaraghavan SV]

Hi all

I am a Professor in Finance. I send the solution files (Excel workbooks) to exam questions to my students. I want my students to open the workbook only once and not again. May be once more !! I tried out Windows Live option. But that adds to my workload and becomes too unwieldy.

Is there is a VBA solution to this requirement? If yes, can anyone help me with a VBA code for this purpose?

Thanks in advance

Regards

Vijay
Chennai, India

 

[sijpie]

There is no foolproof way with VBA, but you can get very close to do it properly It involves some work though.

• When a user opens the file, a Workbook_Open macro needs to run which checks the user windows login name.
• The macro checks the login name against a list of approved users
• If there is no marker behind the name in the list,
  • the macro places a marker after the name in the list and
  • the working sheet(s) are unhidden so that the user can work on the file.
• If there is already a marker behind the name or the name doesnt exist in the list,
  • the user gets a screen (sheet) with a message saying that he no longer is authorised.

In order to make this work there also needs to be a macro that intercepts a save.

• Before the save is done,
  • the real sheet is hidden,
  • the error message sheet is unhidden and
  • the workbook is protected,
• then saved.
• After the save the sheet is made normal again (if the user was approved).

The user doesn't notice any of this.


OK so much for theory, now for practicalities:

• If the files are opened on campus where the users are logged to the network this will work, if they are allowed to download the file and take it home it won't work, because their windows login names are not in the list. So the users will always see the Error screen when trying to open the files outside the Uni network environment.
• You will need to have the full list of the user's network login names to make it work. (Because it uses the network login names, it is pretty difficult to fake being somebody else, you would need their network login password!)
• With Excel 2007+ the password protection of the sheet and in particular of the VBA code is pretty safe
• If the user disables Macros, all that can be seen is a protected workbook, with only the error message sheet visible
• There is nothing to stop the user from screen copying the sheets of course.

 

[sijpie]

I am just rereading your post again.

• In the above example if the student doesn't save the file (this being solutions), then of course there is nothing to be done.
• If you send it to them, they have a copy of it in their mail and they can open it as many times as they want.

If it is on the network then on opening a macro could check the userlist in another file held on the network, check against approved usernames as suggested in my previous post, and if approved open this file. else it would just open the error sheet. The file holding the users list is saved by the macro, so that way it can be made safe. if the file is not available, then the workbook will also refuse to open.
So then it can be achieved

 

[nuked]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    ws.Cells.ClearContents
Next ws

ThisWorkbook.Save

End Sub

 

[sijpie]

nuked, this will not work if the user has been sent the workbook. He can reopen it over and over again. And he can disable macros.

 

[nuked]

I think you're making assumptions about the word 'sent'

 

[Vijayaraghavan SV]

I think you're making assumptions about the word 'sent'

Dear Nuked and Sijpie

Thank you both for your responses.

Let me try to articulate my 'problem'.

I email the solutions (worksheets) to my students by emailing the spreadsheet to the students' google groups id. The worksheets end up in the mail boxes of individual students. So every student has the opportunity to save the file from his inbox into different file names every time!

I do agree to Sijpie's point that you can never prevent anyone from taking screen shots.

I was trying to see if there was indeed a solution to my problem. Nuked's idea that a file empties the contents before it is saved is interesting - but wont the original file which is lying in the inbox that is saved to Temporary folder (while opening with a browser) be still available to the user for opening one more time??

I am confused!!

 

[sijpie]

Nuked's idea that a file empties the contents before it is saved is interesting - but wont the original file which is lying in the inbox that is saved to Temporary folder (while opening with a browser) be still available to the user for opening one more time??

Yes therein lies the problem.
You can force the user to allow macros in order to open the worksheet properly (by showing them only an opening sheet with other sheets hidden) so you then can delete contents or hide them again, even overwriting the file on the local drive (but not in the attachment if the mail is held 'in the cloud').
You could even create a little file somewhere or with some sleuthing a registry entry, which can be checked and if exist the file will not open properly, but it could then still be opened on another PC.

Here is a demo file. You will only be able to open it once, even when downloading it again from the email. To see how it works you will need the password for the VBA code, which can be found on the sheet you see when trying to open the file a second time.

Excel Sheet OpenOnce: https://docs.google.com/file/d/0BxykuY6uKAJ9SWVBQnZ3YmxUY2M/edit?usp=sharing

 

[Vijayaraghavan SV]

Sijpie - Thank you so much. Looks like this solution works. Let me try this out with my students. Million Thanks.

Regards

Vijayaraghavan
Chennai, India

 

[sijpie]

Don't leave the password out on The front page like i did!