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

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

--
Rgards,
Tom Ogilvy
"TBD" wrote in message
...
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!!!

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" wrote:

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

--
Rgards,
Tom Ogilvy
"TBD" wrote in message
...
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!!!

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"

--
Regards,
Tom Ogilvy


"TBD" wrote in message
...
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" wrote:

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

--
Rgards,
Tom Ogilvy
"TBD" wrote in message
...
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!!!

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

"Tom Ogilvy" wrote in message
...
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"

--
Regards,
Tom Ogilvy


"TBD" wrote in message
...
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" wrote:

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

--
Rgards,
Tom Ogilvy
"TBD" wrote in message
...
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!!!

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!

"Tom Ogilvy" wrote:

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

"Tom Ogilvy" wrote in message
...
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"

--
Regards,
Tom Ogilvy


"TBD" wrote in message
...
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" wrote:

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

--
Rgards,
Tom Ogilvy
"TBD" wrote in message
...
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!!!