How to protect cells after one user input?

[Spiker]

Hi folks!

I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.

Any ideas, thanks!

-Ed Chan

[jasoncw]

There might be a simpler method for this, but here is one way. Right-click the sheet tab, select view code, and copy this procedure:

Please Login or Register  to view this content.

You can use this for any number of cells, by copying the green font above and pasting it. As you can see, I have it set up to work for data validation lists in cells B1 and C1. Just change to suit your needs.

Let me know if this doesn't make sense, or if it doesn't accomplish what you are looking for.

Jason

[Spiker]

Hi Jason!

Thanks for your help!!!

I couldn't get it to work, you can stil go back and change the answers. All of the answers are in column B, so I figured if I could set the range to B1:B103 it would work, but it doesn't appear to.

In the Microsoft Visual Basic mode, do I leave the right hand drop down to "Change"?

-Ed Chan

[jasoncw]

Hi, Ed. Did you copy the entire code that I posted above? After you paste it, there should be nothing on the screen other than that code. If upon entering VBA by right-clicking the sheet tab, a Worksheet_SelectionChange event is automatically created, delete it before copying the code I posted. Let me know whether that works or not.

Jason

[Spiker]

I did try copying the entire code, it didn't work. Not sure what I'm doing wrong, I can still edit the data multiple times.

-Ed

[jasoncw]

Hi again, Ed. If your data isn't confidential, can you post up a copy of the zipped file so I can take a look at it?

Jason

[Spiker]

Hi Jason!

Attached is one of the spreadsheets, thanks mucho for your assistance!

-Ed

[jasoncw]

Hi, Ed. It looks like there are 2 issues. First, you did not change the ranges for your questions. It still showed B1, but the answers are in column E. What I did was selected all of the cells for answers, and named the range as "Answers". Then I changed Range("B1") to Range("Answers") to use the correct range.

Second, unfortunately, it looks like if you use a list with data validation, it does not call the worksheet change event handler procedure. However, if you manually enter the selections (rather than using ranges), it works. Not sure why that is. I entered this into the first answer for you to show you what I am talking about (check the validation I used on this cell).

HTH

Jason

[Spiker]

Hi Jason!

Thanks for taking the time to look at this. I understand what you're saying about the data validation, if I have to type it in manually that's fine.

But it doesn't seem to work, you can modify the answer until you get it correct. Did it work for you?

-Ed

[jasoncw]

This turned out to be a little more complicated than I first anticipated (isn't it usually?). I think I have most of the bugs worked out. One additional item was that you could not reset all answers to blanks, so I included a password feature that allows you to enter delete in the cells. See VBA code to change the password.

Jason

[Spiker]

Hi Jason!

It's still not working for me, did it work for you?

-Ed

By the way, if we can get this working, I would like to compensate you for your time on this.

[jasoncw]

Hmmm, that is odd. It works just fine for me. I tried it on both Excel 97 and 2003, and it works on both. What exactly is it doing for you?

[Spiker]

It isn't preventing me from changing the answer, you can modify the answer until you pick the correct one.

-Ed

[jasoncw]

I assume you have macros enabled? The settings need to be either on medium or low to run.

Once you select an answer from the drop-down, does the cursor move down to the next row, or does it stay on the answer?

[Spiker]

Cool, it works, thanks!

Please contact me at ed@myfamg.com, I really appreciate your help!

-Ed

[Spiker]

Hi Jason!

I've been trying to copy the code to the 7 other tests, and have been getting run time errors.

If you wouldn't mind, can you assist me in copying the code to other spreadsheets?

Thanks for all your help and hard work,

-Ed