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
.
|