Stop Student Cheating

**jcmckeon** · 05-01-2018, 11:40 AM

Hello All, this is a Cross-post. I've had some great ideas, but wanted to broaden the input. Please respond to this original posting.

I'm an adjunct instructor. My Excel tests and homework are "shared". Usually I can catch them when I review the formulas because I recognize the same formula error or weird spelling.

Is there anyway stop sharing files especially during a test. I just had a case where one student emailed his test to another student during the test. I found them out and...well...things didn't work out so well for them.

What ideas do you have to keep track of whether they email during a test or open the file after it was emailed to them.

I'm open to any and all suggestions to shut this down.

https://www.mrexcel.com/forum/excel-...ing-excel.html

Best Jill

**dominicb** · 05-01-2018, 11:59 AM

Good evening jcmckeon

I can't think of a way of tracking if a file has been passed between your students.
If you don't have many students you can create a "very hidden" worksheet, or a hidden cell with a number reference in it - each student would have a slightly unique file then. The problem with this is that it would be labour intensive as you would have to create a file for each student and would not be able to send these out in a single "bulk send" e-mail. It would probably catch them out though ...

HTH

DominicB

**sandy666** · 05-01-2018, 12:02 PM

but you must to know they can simply copy result and paste to txt file and send, or easy print screen. many ways to cheat you

**Kyle123** · 05-01-2018, 03:29 PM

Pull the network cable out?

**protonLeah** · 05-01-2018, 08:36 PM

One possibility would be to use a worksheet open macro to save/concatenate the name of the person who opens it to a custom document property.

**scottiex** · 05-01-2018, 09:41 PM

Maybe post a list of user ID (or whatever you can get) who opened it (and maybe other data like where they write their own name etc) to a very hidden sheet.

Then maybe have a macro to collate all that once you have all the tests back if you want to easily see if the same people appear on different sheets.

Also (maybe I'm getting over enthusiastic here)
I suppose it would also be possible to do what you are doing by eyeball automatically - i.e. to look for sheets that were identical over a range of cells - maybe collect some sort of a key (or a few keys) by adding the contents of enough cells together that any duplication is very likely cheating. then consolidate all that into a file and highlight duplicates with corresponding file name or userid. Last edit date might be useful too to guess who cheated from who.

**MarvinP** · 05-01-2018, 09:54 PM

How about

https://www.extendoffice.com/documen...e-in-cell.html

And put the username in a cell that you font to white on white so it isn't seen.

**sandy666** · 05-01-2018, 10:05 PM

or username with ;;;

**jcmckeon** · 05-02-2018, 07:48 AM

How can I do this?

**jcmckeon** · 05-02-2018, 08:17 AM

Hi Ben, how can I do this?

**dominicb** · 05-02-2018, 08:47 AM

Hi jcmckeon

Incorporate your tests into the attached file.
The user must elect to run macros on start-up.
When the file is opened, the Excel username will be posted into cell A1 of a xlSheetVeryHidden sheet called "User".
To expose this sheet use the hot-key combination Shift + Ctrl + Q. This will toggle the sheet's state between xlSheetVeryHidden and Visble. Obviously, the file needs to be distributed with it set to xlSheetVeryHidden.
The VBE has been locked to keep nosey students out, the password is jcmckeon.

HTH

DominicB

**sandy666** · 05-02-2018, 09:55 AM

use a SHIFT key and Open file

and there is no any password

**dominicb** · 05-02-2018, 10:09 AM

Originally Posted by sandy666

use a SHIFT key and Open file

I haven't said that it is impossible to open a file without allowing macros to run - just that macros have to be allowed to run for this to work.
There is no way around that despite attempts from some parties at making it so.

There is also no way that this would fool someone who knows what they're doing, there is also no way of making this foolproof - I am well aware of that. What we're attempting is stopping the some students from all handing in the same file, with the minimum of work.

The ideal situation (and least likely to get figured out by the students) would be what I suggested back in post #2. Unfortunately, this is also the most labour intensive.

DominicB

**jcmckeon** · 05-02-2018, 10:13 AM

This is really neat. Here is a rub. Many of them have MACs so I make them use the Citrix Virtual Desktop. So the name displays as (see attached) mandatory. Can I still capture the user name but also do something like this:

The user name is automatically being captured - that's fine
I'll add a sheet where they have to type there full name. That name then gets transferred to this hidden sheet.
If they "share it" with a friend and the friend now deletes the name of the first sheet and adds their name, can I have this appear of the hidden sheet below the original name.

Get it that way I can see if they are passing the same sheet.

Thoughts? By the way, thank you for your help.

**sandy666** · 05-02-2018, 10:20 AM

@dominicb
We all know excel security level is 0
We don't know Excel knowledge level of the students
So your idea is fine but extend it about compare username stored in hidden sheet with username taken on open file, if they are the same - ok, if not - file selfdelete

**dominicb** · 05-02-2018, 10:38 AM

Hi jcmckeon

There is a way of getting the Windows login name of a user, but once you start using Citrix, all bets are off.

Upload a file that will form the bones of your test, and I'll add a hidden sheet that will log every user that puts their name in the "Student Name" box, so if one user completes the test and passes the form on, you will be able to see who did the test first and any subsequent users of that test.

HTH

DominicB

**AliGW** · 05-02-2018, 10:44 AM

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

No further help to be offered, please, until the OP has complied with this request.

**jcmckeon** · 05-02-2018, 11:42 AM

Understood and again my apologies.

**AliGW** · 05-02-2018, 12:59 PM

You've made a bit of a mess, so I have fixed it, and I have also got rid of the extraneous posts to tidy things up.

**jcmckeon** · 05-02-2018, 01:00 PM

DominicB

Here is a template. Thank you so much in advance for your help. You don't know how important this is to me.

**sandy666** · 05-02-2018, 01:01 PM

corrected: Today at 06:57 PM

**jcmckeon** · 05-02-2018, 01:05 PM

You're awesome!

**scottiex** · 05-02-2018, 05:32 PM

I think at least you would want to have multiple entries for the user because in most cases I imagine the user will open the sheet they are using to cheat and you don't want something as simple as that to ruin the system.

Please Login or Register  to view this content.

if this happens on a shared drive/network (as i guess happens in a test?) all this could be being written to an external file if you prefer.

Please Login or Register  to view this content.

obviously IP address, computer name, other environment data, current file size and used cell count, or whatever else can be added, but you need to decide which are useful (to not give many false positives and detect a reasonable number of true cases) before we complicate the code with it and maybe this is sufficient already.

One interesting thing to watch for would be a file edited only once at a time just minutes before you were emailed the final copy (i.e. they opened it and copied in some stuff).

**protonLeah** · 05-02-2018, 11:28 PM

The code below adds a custom document property "MY_Student_List" the the workbook.
Whenever the workbook is opened, it adds the user name to the list. It is meant to report the names only to the author but I commented out the
lines which test for the author's name before reporting the list, to demonstrate the report:

Please Login or Register  to view this content.

**sandy666** · 05-03-2018, 12:55 AM

just for fun:

custom.xml
core.xml

and takeover

**jcmckeon** · 05-03-2018, 08:10 AM

Hi protonLeah - this is fantastic. The only problem is most student are using a Citrix Virtual Desktop because they have mac laptops. I'm teaching to PC version Excel 2016 so the user name is going to be the name associated with the Excel loaded on the VD. I need to capture their names by having them type in a name.

For example sheet 1 can be called Student Info - there would be a cell titled - Put your full name here. Then I want to capture that name EVERY TIME someone reenters it. So Bob types his name and then "shares" with Sally. Sally deletes his name and types hers. I want to capture both Bob then Sally's name.

It think is the only way to do this while on a network VD.

Thoughts? See my attachment!

**sandy666** · 05-03-2018, 08:28 AM

they can do test without name, share file then enter name
they can enter eg. 15 spaces as name and do test then share file and enter name
etc....

only you know Excel knowledge level of your students

**jcmckeon** · 05-05-2018, 02:58 PM

Originally Posted by jcmckeon

DominicB

Here is a template. Thank you so much in advance for your help. You don't know how important this is to me.

Hello DominicB - were you able to look at my attached file per your last request?

Looking forward to a reply. Jill

**scottiex** · 05-06-2018, 04:33 PM

if username gives the same result (is useless to you) then you can replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

in proton's, dominic's or my code to use the users name on the sheet instead.

I'm not so sure that this is the only way for you to check, but its hard to tell from here.

**jcmckeon** · 05-07-2018, 08:55 AM

Originally Posted by scottiex

if username gives the same result (is useless to you) then you can replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

in proton's, dominic's or my code to use the users name on the sheet instead.

I'm not so sure that this is the only way for you to check, but its hard to tell from here.

Hi Scottiex, I'm not that adapt at VB - I know enough to be dangerous

Also I can't see the code as it is hidden and I don't know how to unlock.

1. Would you please modify the attached file template for me and show me how to view the hidden sheet and hidden code please!!!

2. Also any other suggestion for perhaps - locking the file after students save and submit? It there a way to prevent them from opening the file after they submit? So they finish the test, submit and then the file left on their PC is locked and cannot be open without a password - sort of a 1 and done scenario.

Again, this is VERY important to me and appreciate all the time everyone is spending.

**scottiex** · 05-07-2018, 10:32 PM

dominic's code isn't actually hidden... it is in the thisworkbook module and module 1. Anyway i locked mine with "thesheet" as the password - if you cant edit the code with any confidence you probably don't need to see it.

press hot-key combination Shift + Ctrl + Q and the sheet will disappear until you press that combo again (this is dominics method so same as his)

this sheet now uses the name and adds items below each other.

What we could code depends on your situation and exactly what your students need to be able to do. Not sure exactly what you are preventing with your proposed strategy, you already have a record of every time they open the file and it's time here... but with yours you might end up with a panicked (but honest) student who accidentally submitted something?

**jcmckeon** · 05-08-2018, 08:41 AM

Hi Scottiex,

Thank you for the file. I'm going to take a peek once I give my final exam. The purpose of locking the Excel workbook so you can't open it a second time is to prevent student's from showing the test to another student who is taking the exam at another time. I can't regulate students to delete the file after they submit it to Blackboard but maybe I stop them from passing the file around. So they download once to take the test, then once they close it and submit, it needs an admin password to open the file again. If a student has a true emergency and needs to open the file, I can do it with my admin password.

Is that even possible? More later. I'm off to give an exam. Hooray!

**jcmckeon** · 05-14-2018, 12:32 PM

Hi Scottiex, this worked great. Thank you

Question, is there a way to make an Excel file open only once and then it need a password. After they upload the file for grade, they won't be able to open it on their computers again.

Thoughts?

Again thank you for the help. You are a life saver.

**scottiex** · 05-15-2018, 05:00 PM

Depends on what "upload the file for grade" means.
Do they press a button in the sheet and then something happens where it uploads it to a server? It would be hard for us to code this without a reasonable amount of help from you, as we aren't on your system. I presume currently they just save it to the server.
And it also depends on how much security you want - I think it would be hard to make it so they cant open it at all - but not so hard to make it so they would need a reasonable amount of excel knowledge to use it for cheating.

**jcmckeon** · 05-16-2018, 07:41 AM

Hi Scottiex,

So students log into Blackboard, our learning management software, and launch a test I've made. There is only 1 question - that is an Excel file that they download and open it with Excel outside of Blackboard. They work the problems worksheets and then save the file, adding their names as part of the file name. They then go back to Blackboard, and the test question then upload the Excel file as an attachment.

They then have the original file on their laptops. I want to stop or at least slooooow down their ability to share this file with students who haven't yet taken the test.

99% of the students have little Excel ability and certainly don't have VB scripting. So whatever I do is better then nothing for sure.

Thanks again for your help

**scottiex** · 05-18-2018, 12:26 AM

Well this is a pretty basic protection but maybe it is what you were thinking of

the submit button hides the sheets so they can't see it. that would be how you would expect the sheets to be formatted when you see them on your blackboard.

your ctrl Q shows your sheet and reveals the questions and pressing it again hides the user details again so you are back to a starting format.

Also I have 666 reputation points - I am in need of one more ASAP

**scottiex** · 05-20-2018, 04:24 PM

this one tries to save it too so they don't have a copy to go back to to easily cheat.

It would be interesting to see how effective it is in practice.

**jcmckeon** · 05-21-2018, 07:28 AM

Originally Posted by scottiex

Well this is a pretty basic protection but maybe it is what you were thinking of

the submit button hides the sheets so they can't see it. that would be how you would expect the sheets to be formatted when you see them on your blackboard.

your ctrl Q shows your sheet and reveals the questions and pressing it again hides the user details again so you are back to a starting format.

Also I have 666 reputation points - I am in need of one more ASAP

Hello, First I added a reputation point to get you out of the devils phone number

I like what you've done. Can the function you created with the submit button be done automatically when the workbook closes?

**scottiex** · 05-21-2018, 05:34 PM

Yes it can - but then it might be a pain for you to use as every time you save it (with new questions etc) it will try to reset the format to the submitted format when you want to distribute as un-submitted.
We might be getting too tricky here.

regardless - see attached.

**jcmckeon** · 05-22-2018, 07:21 AM

Scottiex, many thanks - this is awesome. I know it seems like it is too tricky but stopping students from sharing files is the aim. You would not believe how much "borrowing" goes on even for tests. May I send you a gift card to Amazon.com for your troubles? What is your email address?

Thank you for all your help. You'll never know how much I appreciate your help.

**scottiex** · 05-23-2018, 04:49 PM

I had two ideas to make this slightly easier.

this version has a ctrl shift W which you can use when you want to save it. it basically turns off the on close macro.
of course not activating macros when you edit the sheet works too.

so all in one place :
ctrl shift Q is show your data
ctrl shift W is allow you to save it without it reformatting (and hiding the question tabs!)
and password is "thesheet"

i also adjusted it so you can add as many Question sheets as you want - the rule is they start with a "Q"

Also I sent a private message should be up under your notifications.

**scottiex** · 10-02-2018, 05:40 PM

It's been a while so I may have forgotten a little of what our objectives were but hopefully this is an improvement to the project.

Updated with a button they should use to show the questions which will of course require the macros to be enabled, and to record user data when those questions are displayed and ask for their name first (so that data is valid).

**scottiex** · 10-03-2018, 03:38 PM

This one uses that submission sheet effect that I think we wanted.
Try both

Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Re: Stop Student Cheating

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Avoid Cheating Time of Employee

Bookmarks

Bookmarks

Posting Permissions