Case sensitive and single character in cell
 

Hi all,

I have created an attendance database and I am not able to enter all
the required characters in the cell. I need "P", "A", "R" to be entered
in the cell and once entered, when editing, should ask for a password.
The problem in the below code is:
1. It is case sensitive
2. It does not take all the characters as mentioned above
3. Once a wrong data is entered, it is replaced with a blank but
instead I need the previous data which exists.

Please help me on this .

Your help is appreciated.

Thanks ,.
Praveen :)




Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Sets the range to work in
Set hRange = Range("I15:CT36")

'Sets up for any string values (as errors)
On Error GoTo stringval

'Unprotects sheet with password "alcoa" CHANGE PASSWORD HERE AND BELOW
ActiveSheet.Unprotect "alcoa"

'Checks if changed cell was in Column I to CT
If Not Intersect(Target, hRange) Is Nothing Then

'Change was in Col H and value is 30 so - lock cell, reprotect sheet and exit
If Target.Value = "P" Then
Target.Locked = True
'THIS LINE REPLACE WITH NEW PROTECT CODE
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="alcoa"
Exit Sub
End If

'If entry in Col H was nonnumeric or not "P", clear entry from Col H
' and reprotect sheet. Do not lock cell at this time...
stringval:
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True

'AND THIS LINE REPLACE WITH NEW PROTECT CODE
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="alcoa"

End If

End Sub

--------------------


--
praveen_khm
------------------------------------------------------------------------
praveen_khm's Profile: http://www.excelforum.com/member.php...o&userid=30364
View this thread: http://www.excelforum.com/showthread...hreadid=509342