Worksheet_Change to monitor when all required data is entered

[liampog]

Hi there

I want a piece of code to fire when data in 3 columns has been entered. Is there any way to do it?

In column A, the user is required to enter a first name.
In column B, the user is required to enter a last name.
In column C, the user can enter a Date of Birth, but this is optional.
In column D, the user is required to enter a customer number.

I want a piece of code that sorts the list when data has been entered into columns A, B and D.

Is it possible?

At the moment, I have a Worksheet_Change monitoring the D column so that when it is changed (being the last piece of data the user should be entering as it's the right-most column) it fires the code, but ideally it would be great if the sorting code could fire once all the required information has been entered.

Liam

 

[Andrew Poulsom]

What code do you have? You can use IsEmpty on columns A and B to check that they have been filled in.

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Target.Column <= 2 Or Target.Column = 4 Then
            If Cells(Target.Row, "A") <> "" And _
               Cells(Target.Row, "B") <> "" And _
               Cells(Target.Row, "D") <> "" Then
                    [COLOR="Red"]MsgBox "Sort"[/COLOR]
            End If
        End If
    End If
End Sub

 

[Misca]

Try pasteing the following to your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Long
Application.ScreenUpdating = False
Application.EnableEvents = False    'No other events fire while this macro runs
R = Target.Row
If Len(Range("A" & R).Value) * Len(Range("B" & R).Value) * Len(Range("D" & R).Value) > 0 Then
    'Your code here
End If
Application.EnableEvents = True
End Sub

It might be good to make sure only one cell was changed before firing the macro.

 

[liampog]

Hi guys

Thanks for all your replies.

The method I chose was Misca's however I'm curious about how the "IsEmpty" function might be used Andrew?

I have added a little to your code Misca so that the sorting code also gets run if all 4 of the columns data is deleted.

Tried and tested and seems to work pretty nicely.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim R As Integer
    
    Application.ScreenUpdating = False

    If Not Intersect(Target, Range("A3:D1002")) Is Nothing Then
        ActiveSheet.Unprotect
        Application.EnableEvents = False
        R = Target.Row
        If Len(Range("A" & R).Value) * Len(Range("B" & R).Value) * Len(Range("C" & R).Value) > 0 Or Len(Range("A" & R).Value) + Len(Range("B" & R).Value) + Len(Range("C" & R).Value) + Len(Range("D" & R).Value) = 0 Then
            ActiveSheet.Sort.SortFields.Clear
            ActiveSheet.Sort.SortFields.Add Key:=Range("B3:B1002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            ActiveSheet.Sort.SortFields.Add Key:=Range("A3:A1002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            ActiveSheet.Sort.SortFields.Add Key:=Range("D3:D1002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveSheet.Sort
                .SetRange Range("A3:E1002")
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
        Application.EnableEvents = True
        ActiveSheet.Protect
    End If

    Application.ScreenUpdating = True

End Sub