Frank Freese
asked on
Maintain data integrity
Experts,
In my spreadsheet, A12 is date entered, F12 is invoice date and G12 is invoice amount.
If either A12 or F12 are blank you cannot enter data in G12 - OK, that works.
However, the user can go back and delete the date in A12 or F12 or both afterwards. I would like to prevent that from happening such that if there is data in G12 you must have data in A12 and F12.
In my spreadsheet, A12 is date entered, F12 is invoice date and G12 is invoice amount.
If either A12 or F12 are blank you cannot enter data in G12 - OK, that works.
However, the user can go back and delete the date in A12 or F12 or both afterwards. I would like to prevent that from happening such that if there is data in G12 you must have data in A12 and F12.
Something like this?
Sid
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A12")) Is Nothing Then _
If Len(Trim(Range("A12").Value)) = 0 Then Range("G12").ClearContents
If Not Intersect(Target, Range("F12")) Is Nothing Then _
If Len(Trim(Range("F12").Value)) = 0 Then Range("G12").ClearContents
End Sub
Sid
ASKER
interesting idea - can I do this
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A12:A21")) Is Nothing Then _
If Len(Trim(Range("A12:A21"). Value)) = 0 Then Range("G12:G21").ClearCont ents
If Not Intersect(Target, Range("F12:F21")) Is Nothing Then _
If Len(Trim(Range("F12:F21"). Value)) = 0 Then Range("G12:G21").ClearCont ents
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A12:A21")) Is Nothing Then _
If Len(Trim(Range("A12:A21").
If Not Intersect(Target, Range("F12:F21")) Is Nothing Then _
If Len(Trim(Range("F12:F21").
End Sub
ASKER
tried that - it did not work.
Well not exactly.
You want to clear the entire range G12:G21 if say just F14 is empty? Maybe not.
Is this what you want?
Sid
You want to clear the entire range G12:G21 if say just F14 is empty? Maybe not.
Is this what you want?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, acell As Range
Set Rng = Union(Range("A12:A21"), Range("F12:F21"))
If Not Intersect(Target, Rng) Is Nothing Then
For Each acell In Rng
If acell.Value = 0 Then Range("G" & acell.Row).ClearContents
Next
End If
End Sub
Sid
ASKER
real close -
for example, the user can enter data in other cells such as A14:G14. When we clear data can we just go ahead and clear that range A14:G14, as example?
for example, the user can enter data in other cells such as A14:G14. When we clear data can we just go ahead and clear that range A14:G14, as example?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
But there is a hitch.
If the other cell is empty and you type anything in A12, it will still clear the row.
Sid
If the other cell is empty and you type anything in A12, it will still clear the row.
Sid
ASKER
woops - by clear the row - does that include formulars in H12...H14, which are protected
No. Only cells from A to G
Sid
Sid
ASKER
perfect - thats what we wanted - can we try it?
Sure :)
Sid
Sid
ASKER
thank you
Id the data is deleted in A12 or F12 then G12 is cleared automatically?
Sid