vbYesNoCancel

[Nick Vittum]

A little while back, Hans offered me code which, if a userform's close button were clicked, would allow a message box dialog to choose whether or not to also close the Workbook— along with provisions to either close the application, or, if another workbook is open, then close only the workbook to which the userform belongs.

I'm now trying to adapt that code to allow three options:

• Close both form and workbook if Yes is clicked;
• Close only the form if No is clicked;
• Do nothing at all if Close is clicked.

What I've got so far:

Code: Select all

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    On Error Resume Next
    Set wbk = Workbooks("Personal.xlsb")
    On Error Resume Next

    If MsgBox("Do you want to quit Excel as well?", vbYesNoCancel + vbDefaultButton2 + _
            vbQuestion, "Quit Excel") = vbCancel Then GoTo Here
            If vbNo Then
                    HideForm
                    Exit Sub
                End If
            If wbk Is Nothing Then
                    cnt = 1
                    Else: cnt = 2
                 End If
        If Application.Workbooks.Count = cnt Then
            Application.Quit
            Else: ThisWorkbook.Close
       End If
     Exit Sub
  Here:    MsgBox "You're here"
  ShowForm
End Sub

I know it's getting to "Here" when cancel is clicked, because the message displays. And the userform remains displayed until I okay the message. So at that point, why won't it remain (or return)?

Secondly, this seems like an awkward way to go about this. Is there any way to simply cancel the close button, if cancel is clicked?

[Nick Vittum]

I think I understand now why it doesn't work— because the ShowForm macro is called before it's been hidden, and once it's hidden, it's too late to go back. But I can't figure out what to do about it...

[LisaGreen]

Hey Nick,

Consider using a userform.

The plus points are.. you can choose your own colours... you can put as many option buttons on there as you like,,, You can put whatever text on the buttons you want... you can put it wherever you want. You can make it... and the buttons of course whatever size you want.

The min points are.... Coding the buttons... Setting up the userform to recieve input and pass it back to the code.. PS.. I can help with that easy peasy hehehe... deciding what buttons you want and what the captions should be... aaaaaand the colour scheme!

Lisa

[HansV]

A line such as

Code: Select all

            If vbNo Then

makes no sense, since the vbNo does not refer to the result of the MsgBox.

You don't need to use ShowForm - you can cancel closing the userform by setting Cancel to True. This tells Excel to cancel the close action.

Code: Select all

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim wbk As Workbook
    Dim cnt As Long

    Select Case MsgBox("Do you want to quit Excel as well?", vbYesNoCancel + vbDefaultButton2 + _
            vbQuestion, "Quit Excel")
        Case vbCancel
            Cancel = True
        Case vbNo
            HideForm
        Case vbYes
            On Error Resume Next
            Set wbk = Workbooks("Personal.xlsb")
            On Error Resume Next
            If wbk Is Nothing Then
                cnt = 1
            Else
                cnt = 2
            End If
            If Application.Workbooks.Count = cnt Then
                Application.Quit
            Else
                ThisWorkbook.Close
            End If
     End Select
End Sub

[Nick Vittum]

Hey Nick, Consider using a userform.

Ummm. . .

A little while back, Hans offered me code which, if a userform's close button were clicked. . .

Happy Easter!

[Nick Vittum]

You don't need to use ShowForm - you can cancel closing the subform by setting Cancel to True. This tells Excel to cancel the close action. . .

Wow! So simple. And all the hours of searching on the internet—first while I was locked out of the site, and later when all the sensible folk here were gone to bed, or with their families—never revealed this possibility. I suppose once again, I didn't word my search terms quite right.

Thank you so much!

[Nick Vittum]

Code: Select all

    Select Case MsgBox("Do you want to quit Excel as well?", vbYesNoCancel + vbDefaultButton2 + _
            vbQuestion, "Quit Excel")
        Case vbCancel
            Cancel = True
        Case vbNo
            HideForm
        Case vbYes
            On Error Resume Next
            Set wbk = Workbooks("Personal.xlsb")
            On Error Resume Next
            If wbk Is Nothing Then
                cnt = 1
            Else
                cnt = 2
            End If
            If Application.Workbooks.Count = cnt Then
                Application.Quit
            Else
                ThisWorkbook.Close
            End If
     End Select
End Sub

This is so efficient and clear compared to what I was trying to do. I've got to get to a better understanding of the uses of Case. In fact, I think this example actually gives me a better understanding of it than any examples I've looked at up until now.

[Nick Vittum]

Although this code performs splendidly, it does something I did not anticipate which is annoying and disruptive: There are certain pages on which it's critical that the Userform in question be unloaded. Hiding it is not sufficient because it does things behind the scenes even when hidden. So it's set to unload if I move those pages. But, unknown to me, "Unload" and the "Close" button act the same, and now this dialogue is triggered if I go to those pages, whereas before, the form just quietly went away.

I tried

Code: Select all

Application.DisplayAlerts = False

before the Unload command, but that didn't work (because it's not a built-in alert). So I tried adding lines like this, directly to the sub—

Code: Select all

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Application.ActiveSheet = "sums" Then Exit Sub

Also tried ThisWorkbook.ActiveSheet; Also tried

Code: Select all

    If Application.ActiveSheet = Application.Sheets("sums") Then Exit Sub

Every attempt got the same response, "Object does not support this property or method."

Is there a way around this?

[LisaGreen]

Hi Nick,

Application.ActiveSheet will return an object.

If Application.ActiveSheet = "sums" is trying to say if this object = a string.

You need to equate a string with a string.

Try... If Application.ActiveSheet.name = "sums"
... and watch out for upper/Lower case!

Let us all know how you get on!

HTH
Lisa.

[Nick Vittum]

Hi Nick
Try... If Application.ActiveSheet.name = "sums" .

Presto! That worked! Thank you.

[HansV]

Just a side remark: it is not really necessary to use Application.ActiveSheet and Application.Sheets.
If you use ActiveSheet and Sheets without specifying what they belong to, Excel assumes that you are referring to the active workbook.

[LisaGreen]

Hans,

Just a side remark: it is not really necessary to use Application.ActiveSheet and Application.Sheets.
If you use ActiveSheet and Sheets without specifying what they belong to, Excel assumes that you are referring to the active workbook.

Yes.. Easy to forget that.

Lisa

[Nick Vittum]

Just a side remark: it is not really necessary to use Application.ActiveSheet and Application.Sheets.
If you use ActiveSheet and Sheets without specifying what they belong to, Excel assumes that you are referring to the active workbook.

Ah, yes. That was actually where I started—but I forgot to add .Name. And so I kept trying more detailed approaches—but never .Name.

Thanks, to both of you.

[rory]

You can also test if the form is being closed due to clicking the x by testing if the CloseMode argument equals vbFormControlMenu