1 time data entry [Solved/Closed]

Report
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
-
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
-
Hello,
is there any way that make a condition for a cell which don't let to any body change the cell after they enter the date.
i mean when i enter any data such as text number or date i cant change it later.
thanks my friends it will helps me alot

3 replies

Paste this into the VBA for the worksheet:
The field being checked in this case is B2. Change it to suit your requirements.

If the field is blank entry is allowed otherwise a message is produced and the original value is put back into the cell.


Dim oldvalue

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub

oldvalue = Target

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B2"), Target) Is Nothing Then Exit Sub
If oldvalue <> Target And oldvalue <> VbEmptyValue Then
MsgBox ("Cannot change this value")
Target = oldvalue
End If
End Sub

Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
1
Dear RayH
i receive this errors(attached pictures) please help me
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
1
errors


I don't get that error, I am using and .xlsx not .xls

However, replace this line (vbEmptyValue is not a valid constant):
If oldvalue <> Target And oldvalue <> VbEmptyValue Then

with this:
If oldvalue <> Target And oldvalue <> vbNullString Then


This also works for me.
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
1
>
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015

Yep. Thanks for the file. But it was empty, except for 'Test' in B4.

However, I copied my code into it and it worked fine.

I really meant for you to upload the sheet you are working on. The one where you are having a problem.
I see you have several open questions regarding sheet protection etc. . Could your issue be related to that?
I would really need to see your file to be sure.
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
1
Dear Rayh thanks for your responds
could you just upload that Excel for me.
i want if i enter any data in B4 cell i cant change it.
thank you so much.
where do you add the codes?
>
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015

http://speedy.sh/6Gmwv/Arashman-Restrict-Access-to-One-Time-Cell-Entry.xls

http://lmgtfy.com/?q=where+is+the+vba+code+in+excel+located

I made a minor change so that deleting is prevent when selecting multiple cells.
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
1 > RayH
hanks RayH
that was exactly what i want