dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- 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
And if the combination of the job and request number doesn't exist in the monthly sheet, to display the following message box
I do not want both message boxes displayed in any situation.
This is my whole sub
Can someone help me please?
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?