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:
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Michael M

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

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Do you mean at this question ??
VBA Code:
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

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

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So doesn't post #2 answer that for you. ??
 

dpaton05

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

ADVERTISEMENT

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

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

Well-known Member
Joined
Aug 14, 2018
Messages
2,173
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That's perfect, thank you Michael!!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Great... :cool: (y) did you note that I modified the code in post #8 to show how many times NO was pressed ??
 

Watch MrExcel Video

Forum statistics

Threads
1,129,405
Messages
5,636,099
Members
416,898
Latest member
imsorrymen

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