Can I lock a cell after data entry so entry can not be changed

[Verlinde]

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

 

[Gord Dibben]

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

 

[Verlinde]

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]

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