Catching cheating

[Mike_705]

Hi.  We teach a course using Excel as the tech base.  We are looking for ideas on how we might best prevent cheating and create some use records.  Here are two use cases:

1.  Students download a starter file and work a problem set.    Example scenario: student submits it late and gives excuse about sickness/death/computer dies - after starting it on-time of course.  I'd like to know when a student started and worked on an assignment.   We are all for extensions if merited, but we don't want to be taken advantage of.

2. We give a test.  We want students to be able to download the starter sheet and do the test.  After that, we'd like any copies they may have made locally to become unusable - we want to reuse the tests.  After the student finishes the test and uploads, we want only qualified people (e.g. TA or professor) to be able to open the sheet.

My first thought is to do some VB code.  But if we provide an xslm, I'm concerned they save as an xlsx and defeat the macros.  For the test case, this is a problem. 

Or, their excel install will warn about macros on opening them, the students will get confused, and do something wrong.  We have a lot of students and this will create a lot of unnecessary confusion and questions.

I'd welcome any hints about how best to proceed.  Any solution needs to be cross-platform (windows and mac).

Thanks,

   Mike

[#2]

@Mike_705  I have 2 separate answers for you.

1st - for tracking what and when a student enters you can use the track changes features in excel.  you will need to also turn on the legacy menu items for the track changes.  Then you unlock only the cells they should be allowed to type in and then lock the sheet.  

2nd for preventing them from saving it locally you can use VBA to do that.  Basically you can force the sheet to open "blank" or with instructions that they must enable macros to continue.  The VBA can then unhide the content and when the user tries to save the sheet re-hide any and all content before saving.

That said, they can easily work around that by taking a picture of the screen so this approach isn't fool proof.  Maybe add variability that each sheet will randomize the questions and answer order or something like that.

[#3]

@mtarler 

Matt:

    Thanks!  The test solution is awesome.  Yes, they can take pictures, but at least in a classroom we can prevent it.  We force them to leave their phones face down on the table.  So far it's not been a problem.  Given the presently on-line style, it's definitely a concern.  But hopefully by September it won't be?

    The homework idea is good.  I'll have to look at the track changes in excel (only have used it in word).  

Much obliged.

    Mike

[#4]

@Mike_705  you're welcome and good luck.  that track changes feature is hard to find and tricky to use.  I learned about it from a course on how to make part 11 compliant excel worksheets but I'm sure now that you know what to look for you can find other help or webinars about how to find it and use it.