Limit number of entries in quiz sheet

[Guest]

Hi all! I have set up a "fun" office quiz sheet, with a number of questions
with specific one and two-word answers. I have protected the sheet so it is
only possible to enter text into the specific "Answer" Cells. A cell next to
these answers tells the user whether the answer is correct or incorrect.

However, I would like to be able to limit the number of incorrect answers a
user can enter into one cell (for example - 3 attempts), and if they enter an
incorrect answer 3 times the cell becomes locked with their last attempt. Is
it possible to do this?

Any help anyone could give would be much appreciated!!!

 

[Tom Ogilvy]

Sounds like a lot of work, but you could use the change event to react to an
entry:

for a start, See Chip Pearson's page on events

http://www.cpearson.com/excel/events.htm

 

[Guest]

Hi Tom,

Thanks for your help on this, it's much appreciated! Having read Chip
Pearsons "Event Procedure", I have tried to simplify this problem. Basically
now, I would like to lock a cell if the value entered into that very same
cell does not equal a value I have specified in the Event code. For example,
I have done the following very basic code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$8" Then
Aplication.EnableEvents = False
If Target.Value = "Jane Doe" Then
Range("B8").Locked = False
Else
Range("B8").Locked = True
End If
Apllication.EnableEvents = True
End If
End Sub

However, I am consisitently getting de-bug messages. Is the problem with
the code the fact the Target.Address and cell to lock are one and the same
(B8)?

Thanks again!
TBD

 

[Tom Ogilvy]

To unlock/lock a cell, you must remove the sheet protection, unlock/lock the
cell, then readd the sheet protection

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error goto ErrHandler
If Target.Address = "$B$8" Then
Application.EnableEvents = False ' application mispelled (corrected)
Me.Unprotect
If Target.Value = "Jane Doe" Then
Range("B8").Locked = False
Else
Range("B8").Locked = True
End If
Me.Protect
'Apllication.EnableEvents = True ' application mispelled
End If
ErrHandler
Application.EnableEvents
if err.Number <> 0 then
msgbox "There has been an error"
End if
End Sub

If you have used a password, you would need to supply it as well

Me.Unprotect Password:="Mary"

Me.Protect Password:="Mary"

 

[Tom Ogilvy]

The label ErrHandler should have a colon

ErrHandler:

rather than just

ErrHandler

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error goto ErrHandler
If Target.Address = "$B$8" Then
Application.EnableEvents = False ' application mispelled (corrected)
Me.Unprotect
If Target.Value = "Jane Doe" Then
Range("B8").Locked = False
Else
Range("B8").Locked = True
End If
Me.Protect
'Apllication.EnableEvents = True ' application mispelled
End If
ErrHandler:
Application.EnableEvents
if err.Number <> 0 then
msgbox "There has been an error"
End if
End Sub

 

[Guest]

Thanks Tom, that is working an absolute treat now!!! (Though I must remember
to double-check spelling).

I'm now attempting to alter the code so that I can apply it to numerous Cell
references on the one worksheet, if that is indeed possible!

Thanks for your help!