Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I lock a cell after data entry so entry can not be changed
I am creating a test using excel 2003 that will self grade and give the
tester information to review when they get the answer incorrect. What I would like to do is only give them one chance to answer. I do not want them to be able to change their answer. I would like them to enter their answer in a cell. If they get it incorrect, through data validation, a box will pop up to tell them what they need to go back and study. When they close the data validation box, I would like the answer to stay incorrect and the cell to lock so they can not change their answer. Can someone help me with this |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I lock a cell after data entry so entry can not be changed
I would suggest you re-think the Data Validation message part.
If they get it incorrect, Data Validation will pop up a message telling them they have it wrong and "retry" or "cancel". Cancelling will leave the cell blank. You can lock a cell via VBA after any value is entered but I don't know from where you will get a message to pop up when incorrect answer is given. Lookup table and some code maybe? Gord Dibben MS Excel MVP On Wed, 21 Apr 2010 10:20:07 -0700, Verlinde wrote: I am creating a test using excel 2003 that will self grade and give the tester information to review when they get the answer incorrect. What I would like to do is only give them one chance to answer. I do not want them to be able to change their answer. I would like them to enter their answer in a cell. If they get it incorrect, through data validation, a box will pop up to tell them what they need to go back and study. When they close the data validation box, I would like the answer to stay incorrect and the cell to lock so they can not change their answer. Can someone help me with this |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I lock a cell after data entry so entry can not be changed
Well the idea behind the data validation is so when they get it wrong it
tells them what policy to read to get the correct answer. I put Select "ok" to continue which leaves the incorrect answer but I am sure some will go back and change. The main idea is for them to learn the information but I have been requested to see if I can make it unable to change. Any other suggestions? Also, can you put a hyperlink into a data validation error message? "Gord Dibben" wrote: I would suggest you re-think the Data Validation message part. If they get it incorrect, Data Validation will pop up a message telling them they have it wrong and "retry" or "cancel". Cancelling will leave the cell blank. You can lock a cell via VBA after any value is entered but I don't know from where you will get a message to pop up when incorrect answer is given. Lookup table and some code maybe? Gord Dibben MS Excel MVP On Wed, 21 Apr 2010 10:20:07 -0700, Verlinde wrote: I am creating a test using excel 2003 that will self grade and give the tester information to review when they get the answer incorrect. What I would like to do is only give them one chance to answer. I do not want them to be able to change their answer. I would like them to enter their answer in a cell. If they get it incorrect, through data validation, a box will pop up to tell them what they need to go back and study. When they close the data validation box, I would like the answer to stay incorrect and the cell to lock so they can not change their answer. Can someone help me with this . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I lock a cell after data entry so entry can not be changed
I see now. Not thinking clearly when I posted reply.
You just have an Information error message, not a Stop. I don't know how to put a hyperlink in a DV error alert message. Here is some sheet event code which locks any cell after entry. First unlock all cells of the worksheet. Then right-click on the sheet tab and "View Code". Copy/paste the code into that sheet module. Assumes column B is answer column. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then With Me .Unprotect Password:="justme" n = Target.Row If .Range("B" & n).Value < "" Then .Range("B" & n).Locked = True End If End With End If enditall: Application.EnableEvents = True Me.Protect Password:="justme" End Sub Gord On Wed, 21 Apr 2010 19:57:01 -0700, Verlinde wrote: Well the idea behind the data validation is so when they get it wrong it tells them what policy to read to get the correct answer. I put Select "ok" to continue which leaves the incorrect answer but I am sure some will go back and change. The main idea is for them to learn the information but I have been requested to see if I can make it unable to change. Any other suggestions? Also, can you put a hyperlink into a data validation error message? "Gord Dibben" wrote: I would suggest you re-think the Data Validation message part. If they get it incorrect, Data Validation will pop up a message telling them they have it wrong and "retry" or "cancel". Cancelling will leave the cell blank. You can lock a cell via VBA after any value is entered but I don't know from where you will get a message to pop up when incorrect answer is given. Lookup table and some code maybe? Gord Dibben MS Excel MVP On Wed, 21 Apr 2010 10:20:07 -0700, Verlinde wrote: I am creating a test using excel 2003 that will self grade and give the tester information to review when they get the answer incorrect. What I would like to do is only give them one chance to answer. I do not want them to be able to change their answer. I would like them to enter their answer in a cell. If they get it incorrect, through data validation, a box will pop up to tell them what they need to go back and study. When they close the data validation box, I would like the answer to stay incorrect and the cell to lock so they can not change their answer. Can someone help me with this . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock out data entry in a cell | Excel Discussion (Misc queries) | |||
lock cell conditional format but not restrict data entry | Excel Worksheet Functions | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions | |||
Lock Cell After Data Entry | Excel Discussion (Misc queries) | |||
Is there a way to lock out the format for the cell and still allow data entry and C&P? | New Users to Excel |