Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 21st 10, 06:20 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2010
Posts: 2
Default 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   Report Post  
Old April 22nd 10, 12:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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   Report Post  
Old April 22nd 10, 03:57 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2010
Posts: 2
Default 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   Report Post  
Old April 22nd 10, 07:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lock out data entry in a cell DShaver Excel Discussion (Misc queries) 1 August 27th 09 07:29 PM
lock cell conditional format but not restrict data entry GAM Excel Worksheet Functions 1 August 18th 07 06:53 AM
Cell Entry That Locks Selected Cells From Any Data Entry. ron Excel Worksheet Functions 5 February 16th 07 09:52 PM
Lock Cell After Data Entry Ripper Excel Discussion (Misc queries) 1 February 28th 06 08:17 PM
Is there a way to lock out the format for the cell and still allow data entry and C&P? Marc New Users to Excel 1 April 4th 05 07:50 AM


All times are GMT +1. The time now is 12:17 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017