Code to exit MsgBox if Upper Right X is clicked

[L. Howard]

Option Explicit
Option Base 1
Sub DeleteSheets_Warning()
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "You are about to DELETE selected worksheets !" & vbCr & vbCr & _
      "             Are you sure you want to delete" & vbCr & vbCr & _
      "   the sheets you have selected in Column C? "
    
    ' Dialog's Title
    strTitle = "DELETE SHEETS"
 
    'Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo + vbCritical + vbSystemModal, strTitle)
     
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "              NO!"
        Exit Sub
    ElseIf iRet = vbYes Then
        MsgBox "              Yes!"
       DeleteSheets_Claus
    End If
End Sub

This works just fine but for one exception.
If the user gets to the MsgBox as noted in red and decided to cancel out with the X icon in the very upper right, it still calls DeleteSheets_Claus.
I want to exit sub when this happens but don't know how to code:

If MsgBox upper right X icon clicked then exit sub

Thanks.
Howard

 

[chuckchuckit]

Try changing following line in red, and then adding lines in blue:

iRet = MsgBox(strPrompt, [COLOR=#ff0000]vbYesNoCancel[/COLOR] + vbCritical + vbSystemModal, strTitle)
     
' Check pressed button
[COLOR=#0000ff]If iRet = vbCancel Then
     Exit Sub
End If[/COLOR]

 

[L. Howard]

Thanks, chuckchuckit,

I did add a vbCancel but in a bit different manner.

I'm thinking I got it about as idiot proof as this idiot can make it.

Here's what I wound up with. This has to be 'nuff warnings...!

Regards,
Howard

Option Explicit
Option Base 1

Sub DeleteSheets_Warning()
If ActiveCell.Column <> 3 Then
  MsgBox "    Select sheet name/s in Column C.", vbOKOnly
  Exit Sub
End If

If ActiveCell.Value = "" Then
  MsgBox "Column C has no sheet named/s selected.", vbOKOnly
  Exit Sub
End If

    Dim iDel As Integer, jDel As Integer
    Dim strPromptOne As String
    Dim strPromptTwo As String
    Dim strTitle As String
 
    ' Promt
    strPromptOne = "You are about to DELETE selected worksheets !" & vbCr & vbCr & _
      "             Are you sure you want to delete" & vbCr & vbCr & _
      "   the sheets you have selected in Column C? "
      
    strPromptTwo = "                      Click NO to avert sheet deletion..." & vbCr & vbCr & _
      "             Else the sheet/s you have selected in column C" & vbCr & vbCr & _
      "                        Will be permanently deleted !! " & vbCr & vbCr & _
      "                      NO leaves sheet selections intact."
    ' Dialog's Title
    strTitle = "DELETE SHEETS"
    
    iDel = MsgBox(strPromptOne, vbYesNoCancel + vbCritical + vbSystemModal, strTitle)
       If iDel = vbCancel Then
        Exit Sub
       End If
   
       If iDel = vbNo Then
          MsgBox "              NO!"
           Exit Sub
         Else
           jDel = MsgBox(strPromptTwo, vbYesNo + vbSystemModal, strTitle)
          If jDel = vbNo Then
            Exit Sub
          End If
           DeleteSheets_Claus
       End If
End Sub