Making a message box appear in different circumstances

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
We modified this code a couple of weeks ago.....I made adjustments to rectify this very issue....:cool:
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,805
Messages
5,638,468
Members
417,026
Latest member
UDK

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
Top