Previous Topic Next Topic

1 time data entry

Solved/Closed
ArashMan
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 - Jan 14, 2015 at 01:12 AM
ArashMan
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 - Jan 23, 2015 at 11:16 PM
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
RayH
Jan 14, 2015 at 05:29 PM
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
0
ArashMan
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 1
Jan 15, 2015 at 12:18 AM
Dear RayH
i receive this errors(attached pictures) please help me
0
Answer 2 / 3
ArashMan
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 1
Jan 15, 2015 at 12:20 AM
errors


0
Answer 3 / 3
RayH
Jan 15, 2015 at 12:03 PM
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.
0
ArashMan
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 1
Jan 18, 2015 at 06:58 AM
i revived same error in that line again.
thanks anyway
0
RayH
Jan 18, 2015 at 12:13 PM
Have you tried creating a new file and trying it in there? You must have something in your sheet that is causing the error.

I have made it produce the same error by merging two cells together.
Evidently this wont work if the cell you are using is merged with another.

Do you have a merged cell?
0
ArashMan
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 1
Jan 18, 2015 at 11:16 PM
i removed the merged cell but i still have the same error
i will appreciate if you can give me sample file.
thanks
0
RayH
Jan 19, 2015 at 11:09 AM
Perhaps you should post your file.
The only thing in the file I have is the code I provided.
0
ArashMan
Posts
41
Registration date
Monday December 15, 2014
Status
Member
Last seen
February 18, 2015
 1 > RayH
Jan 19, 2015 at 11:13 PM
ok sure the files password is "test"
https://upload7.ir/404/vfont/not.html
0