Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Answer 1 / 3

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
Report
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
Answer 2 / 3
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 1
errors


Answer 3 / 3

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.
View all 10 comments
Report
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 1
https://upload7.ir/404/vfont/not.html

try this one please
Report
>
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.
Report
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?
Report
>
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.
Report
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!