MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Charles Bowser III, RCDD on June 22, 2001 11:28 AM

I have created a test for my employees in Excel that grades itself as they take it. The student selects and answer from a pull down box and then a 1 through 4 is placed in a target cell. How can I make it that they can only select once from the pull down menu? That way they can not go back and change the answer when they see they answered it incorrectly.

Thanks in advance for any help you can offer.

Posted by Joe Was on June 22, 2001 1:01 PM

To work this you must add a one item list with Data - Validation select type list add Done. In this macro code I put the "Done" list in cell "C33" to signal the end of the test.

Then Right click the sheet tab where the test is and the Done list. Select "View Code" and paste the code below, do not change the name or the code will fail!

Note the password is: admin

When the end of the test box is selected and Done selected, the cells that have been locked with Format - Cells - Protection checked "Lock" on your sheet, will be password locked from changes.

Note: You can change the code password to any you want, make a file backup incase you forget your password.

And, change the "Done" cell address to your "Done" cell address.

I tested this and it works.

To unlock the sheet: Tools - Protection - UnProtect Sheet then enter your password.

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro by Joseph S. Was
Application.EnableEvents = False

If Target = [C33] And [C33] = "Done" Then ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub

Posted by Joe Was on June 22, 2001 1:43 PM

Password lock test selections from changes Sheet Tab code

Note: This will only lock the whole sheet. It will only lock all the lock selected cells on a sheet. To lock individual questions, put each question on its own sheet and add the lock code to each sheet. If the test is to be answered in one sitting, then the code is OK the way it is. If one question is tested and then reviewed before the next question is answered then it is best to add each question to its own sheet.

Macro by Joseph S. Was