Making a message box appear in different circumstances

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have code that runs if a job is to be cancelled and a different message box is displayed in different circumstances. With this code however, if No is selected each time you are asked if this is the row, you get both message boxes.

I need for if cancel is pressed on every job to display the following message box
  • VBA Code:
    MsgBox "You have chosen to not cancel any of the " & n & " job/s matching the date and request number."

And if the combination of the job and request number doesn't exist in the monthly sheet, to display the following message box
  • VBA Code:
    MsgBox "There is no job with the date: " & Dt & " and the request number: " & Req & " in the sheet of " & mth & "."

I do not want both message boxes displayed in any situation.

This is my whole sub
VBA Code:
Sub Transfer()
    Dim ws As Worksheet, Sh As Worksheet, Cncl As Worksheet, mth As String, r As Long, lr As Long, answer As String, n As Long, x As Long
    Set Sh = Sheets("Totals")
    Set Cncl = Sheets("Cancellations")
    Dim Req As String: Req = Sh.[B25].Value
    Dim Dt As String: Dt = Sh.[B27].Value
    mth = MonthName(Month(Dt))
    If Day(Dt) >= 26 Then mth = MonthName(Month(Dt) + 1)
    Worksheets(mth).Activate
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    n = 0
    x = 0
        For r = 4 To lr
            If Cells(r, 1).Value <> Dt And Cells(r, 3).Value <> Req Then x = x + 1
            If Cells(r, 1).Value = Dt And Cells(r, 3).Value = Req Then
                'Highlight the cells up to column O for the specified row so the user knows what row the question is relating to
                Range("A" & r & ":" & "O" & r).Interior.ColorIndex = 6
                answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Job Cancellation")
                Rows(r).Interior.ColorIndex = 0
                If answer = vbYes Then
                    'Copy the row number which is the number of the iteration of the for loop
                    Rows(r).EntireRow.Copy Cncl.Range("A" & Rows.Count).End(xlUp).Offset(1)
                    Rows(r).EntireRow.Delete
                    Exit Sub
                End If
                If answer = vbNo Then n = n + 1
            Else

            End If
        Next r
    If n > 0 Then MsgBox "You have chosen to not cancel any of the " & n & " job/s matching the date and request number."
    If x > 0 Then MsgBox "There is no job with the date: " & Dt & " and the request number: " & Req & " in the sheet of " & mth & "."
End Sub

Can someone help me please?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
We modified this code a couple of weeks ago.....I made adjustments to rectify this very issue....:cool:
 
Upvote 0
We did? Maybe I forgot to change it. I looked back in my previous posts but I am not sure what one would have had the code in it.
 
Upvote 0
I am sure though that I would have changed it. I know that I didn't write all of that procedure I provided in post 1. I did write some of it but I am not very familiar with some of the constructs used so I am pretty sure that would have been the one you wrote. The issues have just come to light after further testing.
 
Upvote 0
I think I remember now, you helped me with a LateCancel procedure which is part of the same spreadsheet but it has a different role to the transfer sub described above.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top