Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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.
0
Frank Freese
Asked:
Frank Freese
  • 7
  • 6
1 Solution
 
SiddharthRoutCommented:
Would this solution work for you?

Id the data is deleted in A12 or F12 then G12 is cleared automatically?

Sid
0
 
SiddharthRoutCommented:
Something like this?

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

Open in new window


Sid
0
 
Frank FreeseAuthor Commented:
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").ClearContents

    If Not Intersect(Target, Range("F12:F21")) Is Nothing Then _
    If Len(Trim(Range("F12:F21").Value)) = 0 Then Range("G12:G21").ClearContents
End Sub
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Frank FreeseAuthor Commented:
tried that - it did not work.
0
 
SiddharthRoutCommented:
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?

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

Open in new window


Sid
0
 
Frank FreeseAuthor Commented:
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?
0
 
SiddharthRoutCommented:
You mean this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    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("A" & acell.Row & ":G" & acell.Row).ClearContents
        Next
    End If
    Application.EnableEvents = True
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
But there is a hitch.

If the other cell is empty and you type anything in A12, it will still clear the row.

Sid
0
 
Frank FreeseAuthor Commented:
woops - by clear the row - does that include formulars in H12...H14, which are protected
0
 
SiddharthRoutCommented:
No. Only cells from A to G

Sid
0
 
Frank FreeseAuthor Commented:
perfect - thats what we wanted - can we try it?
0
 
SiddharthRoutCommented:
Sure :)

Sid
0
 
Frank FreeseAuthor Commented:
thank you
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now