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

dpaton05

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

VBA Code:
                                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

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

VBA Code:
                                    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:
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
And you pressed NO twice ??
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,173
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
These are my filters
CSS Work Allocation Sheet.74.xlsm
BCDEFG
22Cancelling a job
23To 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.
24Request number
2595
26Date
275/07/2021
Totals
Cells with Data Validation
CellAllowCriteria
B27Any value
B25Any value


This is my data

CSS Work Allocation Sheet.74.xlsm
ABCDEFGHIJ
45/07/202195Supervised contact$219.30$21.93$241.23
55/07/202195asdfSupervised contact$219.30$21.93$241.23
67/07/202196
July
Cell Formulas
RangeFormula
I4:I5I4=IF(E4="Activities",0,H4*0.1)
J4:J5J4=I4+H4
Cells with Data Validation
CellAllowCriteria
F4:J5Any 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

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If you step through the code when you get to this line
VBA Code:
rng.SpecialCells(xlCellTypeVisible)
how many lines does it say there are ??
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,173
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

The only instance of that bit of code is in the following line
VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Sorry Dave my error....move the n=0 up before the For ....Next loop
VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,173
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Michael, you are AWESOME!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,441
Messages
5,636,305
Members
416,912
Latest member
danluk12

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