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

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

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

.

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

.