Making a message box appear if No is pressed for each time a question is asked

[dpaton05]

I have a procedure that cycles through some visible rows, asking if is this row is the right row. The sub works but if you press no each for each question, I want to display a message msgbox. Could someone show me what is wrong with my code please?

                                Dim EndMsgCounter As Integer
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    For Each RowLine In rng.SpecialCells(xlCellTypeVisible)
                                        ws.Activate
                                        EndMsgCounter = 1
                                        RowLine.EntireRow.Interior.ColorIndex = 6
                                        answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Cancel Job")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'Copy and paste the identified row to the cancellations sheet
                                            RowLine.EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                            RowLine.EntireRow.Delete
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                        
                                            If rws = EndMsgCounter Then
                                                'Display a message box if no is pressed each time
                                                MsgBox "No jobs have been cancelled"
                                                Exit Sub
                                            End If
                                        EndMsgCounter = EndMsgCounter + 1
                                    Next RowLine
                                End If
                                
FoundRightJob:

 

[Michael M]

Change to

If answer = vbNo Then
    MsgBox "You Pressed No !!"
    'exit Sub or some other code response
    Else
    'Copy and paste the identified row to the cancellations sheet
    RowLine.EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
    RowLine.EntireRow.Delete
    'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
    RowNumber = RowLine.Row - 3
    GoTo FoundRightJob
End If

 

[dpaton05]

Thanks for the reply Michael, let me explain a bit.

The code needs to cycle through 1 or more unhidden rows asking if this is the row/job that needs to be cancelled. My code works but if No is pressed for each row, the sub ends with nothing happening but I need a message to appear at the end of the questions, not after each one.

The code I showed in post 1 is my attempt at modifying the code.

Here is my sub that works

Sub Transfer()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, AutoFilterCounter As Long
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        Dim SheetCounter As Integer: SheetCounter = 0
'Call TurnOffFunctionality
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt           ' autofilter for the value in cell [B27]
                                .AutoFilter 3, Req          ' autofilter for the value in cell [B25]
                                                                    'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                    If ws.[A3].Cells.Offset(1, 0) = "" Then
                                        .AutoFilter
                                        SheetCounter = SheetCounter + 1
                                        'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                        If SheetCounter = 12 Then
                                            MsgBox "A job with the date and request number entered does not exist"
                                        End If
                                        GoTo SkipSheet
                                    End If
                                    
                                'Check the count Autofilter
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                'If value less than 2, only the heading is visible so skip to the next sheet.
                                If AutoFilterCounter < 2 Then
                                    .AutoFilter
                                    'Add 1 to a sheet counter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipSheet
                                End If
                                
                                Dim EndMsgCounter As Integer
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    For Each RowLine In rng.SpecialCells(xlCellTypeVisible)
                                        ws.Activate
                                        RowLine.EntireRow.Interior.ColorIndex = 6
                                        answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Cancel Job")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'Copy and paste the selected row
                                            RowLine.EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                            RowLine.EntireRow.Delete
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            'RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                        
                                    Next RowLine
                                End If
                                
FoundRightJob:

                                .AutoFilter                 ' turn off the autofilter
                        End With
                End If
SkipSheet:
        Next ws


'sh.Range("B25,B27").ClearContents
'Call TurnOnFunctionality
End Sub

 

[Michael M]

Do you mean at this question ??

answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Cancel Job")

AND
What should happen if they press NO, apart from the MsgBox

 

[dpaton05]

Yes, that is the question. If I press No each time, I just want the message box to appear.

If Yes is pressed for a row, it is moved to the cancellations sheet.

I just modified post 3 with my whole sub, in case you missed it.

 

[Michael M]

So doesn't post #2 answer that for you. ??

 

[dpaton05]

The code in post 2 does display a message box after each time but, for instance:

• If there are 2 rows, the question will be asked twice
• If the user presses No for all the questions, then I wanted a message box displayed saying "You did not select a date"

 

[Michael M]

Maybe this ay

For Each RowLine In Rng.SpecialCells(xlCellTypeVisible)
            n = 0
            ws.Activate
            RowLine.EntireRow.Interior.ColorIndex = 6
            answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Cancel Job")
            RowLine.EntireRow.Interior.ColorIndex = 0
            If answer = vbYes Then
                'Copy and paste the selected row
                RowLine.EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
                RowLine.EntireRow.Delete
                'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                'RowNumber = RowLine.Row - 3
                GoTo FoundRightJob
            End If
        If answer = vbNo Then n = n + 1
        Next RowLine
    If n > 0 Then MsgBox " you have chosen not to cancel" & n & " jobs !!"
    End If
End Sub

 

[dpaton05]

That's perfect, thank you Michael!!

 

[Michael M]

Great... did you note that I modified the code in post #8 to show how many times NO was pressed ??