Msg Box running the code after selecting cancel

[RAJESH1960]

Hello code experts

I am trying to check the whether the headings in the pasteData sheet are matching with the CopyData sheet with Msg boxes. If they are matched, then the code has to run the rest of the code. If they are not matched then it has to exit sub.
First the code has to check for only one heading “Supplier Invoice No. & Date” the most wanted heading required in the paste data sheet. If it is not available, then the code should display a msg box that the “Supplier Invoice No. & Date” is missing with a single button Ok and if I press Ok, I should be able to exit the Sub. So that I can correct the Paste data sheet and run the code again. If it is available, then continue with the next line to check the other headings which are not matching.
After inserting the “Supplier Invoice No. & Date” column, when again I run the code, it should check if there are headings in the paste data which are not in the copy data sheet, it will display a Msg box a message =The name of the headings not available + “not in the database” . Here again if there are mismatches and I press cancel, the code should exit the sub else run the rest of the code i.e., to clear the data and display msg box “All data cleared.”

The problem is in both the cases, the code is running the rest of the code and clearing the data, even after pressing cancel.

Need your expertise to solve this issue.
MisMatch Headings exit sub.xlsm

 

[rlv01]

I can't open your file, but you should be looking for the message boxes to have a structure along these lines.

    '1st
    If MsgBox("Supplier Invoice No. & Date is missing", vbOKOnly Or vbExclamation, Application.Name) = vbOK Then
        Exit Sub
    End If

    '2nd
    Dim HeadingNames As String
    HeadingNames = "Heading 23" 'as an example
    
    If MsgBox(HeadingNames & " not in the database", vbOKCancel Or vbExclamation, Application.Name) = vbCancel Then
        Exit Sub
    End If

 

[RAJESH1960]

I can't open your file, but you should be looking for the message boxes to have a structure along these lines.

    '1st
    If MsgBox("Supplier Invoice No. & Date is missing", vbOKOnly Or vbExclamation, Application.Name) = vbOK Then
        Exit Sub
    End If

    '2nd
    Dim HeadingNames As String
    HeadingNames = "Heading 23" 'as an example
   
    If MsgBox(HeadingNames & " not in the database", vbOKCancel Or vbExclamation, Application.Name) = vbCancel Then
        Exit Sub
    End If

This is the code I have in the workbook

Option Explicit

Sub ClearOldWorkings()
'
Application.ScreenUpdating = False

    Dim x, rng1 As Range, rng2 As Range
    Set rng1 = Sheets("pastedata").Cells(1).CurrentRegion
    x = Application.Match("Supplier Invoice No. & Date", rng1.Rows(1), 0)
    If IsError(x) Then MsgBox """Supplier Invoice No. & Date"" is missing", vbCritical: Exit Sub
    Set rng2 = Sheets("copydata").Cells(1).CurrentRegion
    rng1.Rows(1).Interior.ColorIndex = xlNone
    x = Filter(rng1.Parent.Evaluate("if(isna(match(" & rng1.Rows(1).Address & "," & _
    rng2.Rows(1).Address(, , , 1) & ",0))," & rng1.Rows(1).Address & ")"), False, 0)
    If UBound(x) > -1 Then
        If vbOK = MsgBox("Missing in DataBase" & vbLf & vbLf & _
            Join(x, ":" & vbLf), vbInformation + vbOKCancel, "Continue?") Then
            Set rng1 = rng1.Resize(, rng1.Columns.Count + 1)
            x = rng2.Parent.Evaluate("iferror(match(" & rng2.Rows(1).Address & "," & _
                rng1.Rows(1).Address(, , , 1) & ",0)," & rng1.Columns.Count & ")")
            x = Application.Index(rng1, Evaluate("row(2:" & rng1.Rows.Count & ")"), x)
            rng2.Rows(rng2.Rows.Count + 1).Resize(rng1.Rows.Count - 1) = x
        End If
    End If
    rng1.Rows(1).Interior.ColorIndex = xlNone

    
    Sheets("CopyData").Range("A2:H2", Sheets("CopyData").Range("A2:AH2").End(xlDown)).ClearContents
    Application.ScreenUpdating = True
    MsgBox "Old Data Cleared."
End Sub

 

[6StringJazzer]

Here is how the code is actually written:

If "Supplier Invoice No. & Date" is not found, the MsgBox is shown and the Sub exits. It can't be doing anything else. If you get that message, it's going to exit.

On the second one, if you show the MsgBox, and click OK, it's going to do the code immediately following, and then finish the If/End If. After that, no matter has happened up to this point, it's going to execute the rest of the Sub:

    rng1.Rows(1).Interior.ColorIndex = xlNone

    
    Sheets("CopyData").Range("A2:H2", Sheets("CopyData").Range("A2:AH2").End(xlDown)).ClearContents
    Application.ScreenUpdating = True
    MsgBox "Old Data Cleared."

If you want the sub to exit if you click cancel then you need this change:

    If UBound(x) > -1 Then
        If vbOK = MsgBox("Missing in DataBase" & vbLf & vbLf & _
            Join(x, ":" & vbLf), vbInformation + vbOKCancel, "Continue?") Then
            
            Set rng1 = rng1.Resize(, rng1.Columns.Count + 1)
            x = rng2.Parent.Evaluate("iferror(match(" & rng2.Rows(1).Address & "," & _
                rng1.Rows(1).Address(, , , 1) & ",0)," & rng1.Columns.Count & ")")
            x = Application.Index(rng1, Evaluate("row(2:" & rng1.Rows.Count & ")"), x)
            rng2.Rows(rng2.Rows.Count + 1).Resize(rng1.Rows.Count - 1) = x
         
        Else
           Exit Sub
        End If
    End If

 

[RAJESH1960]

Here is how the code is actually written:

If "Supplier Invoice No. & Date" is not found, the MsgBox is shown and the Sub exits. It can't be doing anything else. If you get that message, it's going to exit.

On the second one, if you show the MsgBox, and click OK, it's going to do the code immediately following, and then finish the If/End If. After that, no matter has happened up to this point, it's going to execute the rest of the Sub:

    rng1.Rows(1).Interior.ColorIndex = xlNone

   
    Sheets("CopyData").Range("A2:H2", Sheets("CopyData").Range("A2:AH2").End(xlDown)).ClearContents
    Application.ScreenUpdating = True
    MsgBox "Old Data Cleared."

If you want the sub to exit if you click cancel then you need this change:

    If UBound(x) > -1 Then
        If vbOK = MsgBox("Missing in DataBase" & vbLf & vbLf & _
            Join(x, ":" & vbLf), vbInformation + vbOKCancel, "Continue?") Then
           
            Set rng1 = rng1.Resize(, rng1.Columns.Count + 1)
            x = rng2.Parent.Evaluate("iferror(match(" & rng2.Rows(1).Address & "," & _
                rng1.Rows(1).Address(, , , 1) & ",0)," & rng1.Columns.Count & ")")
            x = Application.Index(rng1, Evaluate("row(2:" & rng1.Rows.Count & ")"), x)
            rng2.Rows(rng2.Rows.Count + 1).Resize(rng1.Rows.Count - 1) = x
        
        Else
           Exit Sub
        End If
    End If

Your understood my problem very well. It is solved now. Thanks Jeff.