Option to Exit Macro Via Message Box

**kirsty** · 12-11-2010, 09:00 PM

Hello, I am wondering how to write a code that checks the number of workbooks open and, if it is more that one, pops up a message box giving the option to exit the macro by pressing no or cancel. I have successfully gotten the message box to show but cannot get the additional NO button to work correctly. Thank you!

**pike** · 12-11-2010, 11:58 PM

Hi kirsty
hard to say with out code but maybe...

Sub CountWorkbooks()
    Dim Xresponse As Integer
    Xresponse = MsgBox("More that one workbook open." & Chr(10) & "Workbooks Open =" & Windows.Count, vbYesNo + vbCritical)
    Select Case Xresponse
    Case 6
        MsgBox ("Yes")
    Case 7
        MsgBox ("No")
    End Select
End Sub

**kirsty** · 12-12-2010, 12:15 AM

Thank you very much pike. This works to get both Yes and No buttons to show however neither response exits the macro on my computer? If I answer "No" I need the macro to exit. Do you know how to modify the code to do that?

**pike** · 12-12-2010, 12:24 AM

Hi kirsty

Sub CountWorkbooks()
    Dim Xresponse As Integer
    Xresponse = MsgBox("More that one workbook open." & Chr(10) & "Workbooks Open =" & Windows.Count, vbYesNo + vbCritical)
    Select Case Xresponse
    Case 6
        MsgBox ("Yes")
    Case 7
        MsgBox ("No")
        Exit Sub    ' This line will exit the sub
    End Select


End Sub

**kirsty** · 12-12-2010, 02:05 AM

Hey, thank you very much. I was able to tweak it to do what I needed. Have a nice weekend!

**JBeaucaire** · 12-12-2010, 10:05 AM

FYI, all....

Since you only really need to "do" something specific if one answer is given, you can slim that down to one command:

Sub CountWorkbooks()

   If MsgBox("More that one workbook open." & vbLf & "Workbooks Open = " & Windows.Count _
       & vbLf & vbLf & "Do you wish to abort?", vbYesNo + vbCritical) = vbYes Then Exit Sub

End Sub