Ndtyjky

I am using a userform to update data in a worksheet, I have an update command button to copy the data from the 'data' worksheet to the 'archive' and replace in the 'data' worksheet (essentially the 'archive' is a log of all previous lines and the 'data' is the most recent information)

The information is changed in text boxes and combo boxes

What Im struggling with is for the 'update' cmdbutton to first check if any changes where made before copying the data, if not I want a msg box to read 'no change in data, please close form'

Here is the code for the userform so far:

Private Sub cmdUpdate_Click()

' To write edited info of userform2 to Sheets("Data")

Dim LastRow As Long

Dim ABnum As Double

Dim ABrng As Range

Dim WriteRow As Long



'error statement

On Error GoTo errHandler:

'hold in memory and stop screen flicker

Application.ScreenUpdating = False

' Make sure we're on the right sheet

With Sheets("Data")

' Get the last row used so can set up the search range

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

' Set the range to search for the AB number

    Set ABrng = .Range("A1:A" & LastRow)

' Get the AB number from what is selected on userform2

    ABnum = txtup1.Value

' Get the row of sheet for this AB number

    WriteRow = Application.Match(ABnum, ABrng, 0)

' Make this AB number the active cell

    With .Cells(WriteRow, 1)

' Write in all the editable options

    Sheets("Archive").Range("A" & Rows.Count).End(xlUp)(2).Resize(, 14).Value = .Resize(, 14).Value

        .Offset(0, 4) = cboup3.Value

        .Offset(0, 5) = cboup4.Value

        .Offset(0, 6) = cboup5.Value

        .Offset(0, 7) = cboup6.Value

        .Offset(0, 8) = Date

        .Offset(0, 9) = txtrev.Value

        .Offset(0, 12) = txtup9.Value

        .Offset(0, 13) = txtup8.Value

    End With

End With

' Filter the Data

FilterMe

' Close the form

Unload Me



MsgBox ("Enquiry E0" + Me.txtup1.Text + " has been updated")



errHandler:

'Protect all sheets if error occurs

'Protect_All

'show error information in a messagebox

If Err.Number <> 0 Then

    MsgBox "Error " & Err.Number & " just occured."

End If



End Sub

The easiest way would be to write a function to compare the values.

Private Sub cmdUpdate_Click()

' To write edited info of userform2 to Sheets("Data")

    Dim LastRow As Long

    Dim ABnum As Double

    Dim ABrng As Range

    Dim WriteRow As Long



    'error statement

    On Error GoTo errHandler:

    'hold in memory and stop screen flicker

    Application.ScreenUpdating = False

    ' Make sure we're on the right sheet

    With Sheets("Data")

        ' Get the last row used so can set up the search range

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        ' Set the range to search for the AB number

        Set ABrng = .Range("A1:A" & LastRow)

        ' Get the AB number from what is selected on userform2

        ABnum = txtenqup.Value

        ' Get the row of sheet for this AB number

        WriteRow = Application.Match(ABnum, ABrng, 0)

        ' Make this AB number the active cell

        With .Cells(WriteRow, 1)

            'Check for changes



            If Not hasValuePairsChanges(.Offset(0, 4).Value, cboup3.Value, _

                                        .Offset(0, 5).Value, cboup4.Value, _

                                        .Offset(0, 6).Value, cboup5.Value, _

                                        .Offset(0, 7).Value, cboup6.Value, _

                                        CDate(.Offset(0, 8).Value), Date, _

                                        CDbl(.Offset(0, 9).Value), CDbl(txtrev.Value), _

                                        .Offset(0, 12).Value, txtnotes.Value, _

                                        .Offset(0, 13).Value, txtdtime.Value) Then

                MsgBox "No Change in Data", vbInformation, ""

                Exit Sub

            End If



            ' Write in all the editable options

            Sheets("Archive").Range("A" & Rows.Count).End(xlUp)(2).Resize(, 14).Value = .Resize(, 14).Value

            .Offset(0, 4) = cboup3.Value

            .Offset(0, 5) = cboup4.Value

            .Offset(0, 6) = cboup5.Value

            .Offset(0, 7) = cboup6.Value

            .Offset(0, 8) = Date

            .Offset(0, 9) = txtrev.Value

            .Offset(0, 12) = txtnotes.Value

            .Offset(0, 13) = txtdtime.Value

        End With

    End With

    ' Filter the Data

    FilterMe

    ' Close the form

    Unload Me



    MsgBox ("Enquiry E0" + Me.txtenqup.Text + " has been updated")

errHandler:
'Protect all sheets if error occurs
'Protect_All
'show error information in a messagebox
If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & " just occured."
End If

End Sub



Function hasValuePairsChanges(ParamArray Args() As Variant) As Boolean

    Dim n As Long



    For n = 0 To UBound(Args) Step 2

        If Not Args(n) = Args(n + 1) Then

            hasValuePairsChanges = True

            Exit Function

        End If

    Next

End Function