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:

 

[dpaton05]

I didn't notice that but thanks. I tried to change it and had a problem.

I have 2 rows/jobs but the message box is only telling me there is 1 job.

                                    Dim answer As Integer, n As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    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 any of the " & n & " job/s !!"
                                End If
                               
FoundRightJob:

 

[Michael M]

And you pressed NO twice ??

 

[dpaton05]

These are my filters

CSS Work Allocation Sheet.74.xlsm
	B	C	D	E	F	G
22	Cancelling a job
23	To cancel a job, enter the request number in B25 followed by the date of the proposed job in B27 then press enter and select the cancel the Cancel a job button.
24	Request number
25	95
26	Date
27	5/07/2021
Totals

Cells with Data Validation
Cell	Allow	Criteria
B27	Any value
B25	Any value

This is my data

CSS Work Allocation Sheet.74.xlsm
	A	B	C	D	E	F	G	H	I	J
4	5/07/2021		95		Supervised contact			$219.30	$21.93	$241.23
5	5/07/2021		95	asdf	Supervised contact			$219.30	$21.93	$241.23
6	7/07/2021		96
July

Cell Formulas
Range	Formula
I4:I5	I4	=IF(E4="Activities",0,H4*0.1)
J4:J5	J4	=I4+H4

Cells with Data Validation
Cell	Allow	Criteria
F4:J5	Any value

I run the procedure and

• it filters the data to show the 2 rows.
• The first row is highlighted and I am asked the question is this the row you want to cancel?
• I press No and the next row is highlighted and am asked the same question
• I press No again and get the msgbox You have chosen not to cancel any of the 1 job/s.

I want it to say You have chosen not to cancel any of the 2 job/s, as there were 2 filtered jobs.

Thanks Michael.

 

[Michael M]

If you step through the code when you get to this line

rng.SpecialCells(xlCellTypeVisible)

how many lines does it say there are ??

 

[dpaton05]

The only instance of that bit of code is in the following line

For Each RowLine In rng.SpecialCells(xlCellTypeVisible)

When it gets to that line, there are 2 rows visible. Is that what you meant by asking how many lines does it say there are?

 

[Michael M]

I understand that there are 2 visible rows....what I need to know is does the code know there are 2 visible rows ??
Can you upload the workbook ??

 

[Michael M]

Sorry Dave my error....move the n=0 up before the For ....Next loop

n = 0
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 Then n = n + 1
Next RowLine
If n > 0 Then MsgBox " You have chosen not to cancel any of the " & n & " job/s !!"
End If

d If

 

[dpaton05]

Thanks Michael, you are AWESOME!!