Userform Cancel

[Hank Youngerman]

I am always amazed at how I can't find answers to the simplest
questions.

I am loading a userform. The form has the normal "close" box in the
upper right corner.

It appears that if that box is clicked, the form unloads. This can
cause some problems with my later program flow. I have an "exit" box
on the form itself, but that lets me run some code.

How do I capture the event where the user clicks the "x" to close the
form? I have events like workbook_beforeclose but I can't find a
similar event for a form. Alternatively, how do I eliminate the close
box from the form, so that the user has to click on "exit?"

 

[Susan]

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button to close the form", vbInformation

End If
End Sub
xxxxxxxxxxxxxxxxx
i don't need this, usually, but somebody posted it once & i snatched it
"in case".
susan

 

[Bob Phillips]

There are a number of ways the close can be invoked, all can be handled via
query close. This extract from help shows the values for CloseMode

vbFormControlMenu 0 The user has chosen the Close command from the
Control menu on the UserForm.
vbFormCode 1 The Unload statement is invoked from code.
vbAppWindows 2 The current Windows operating environment session is
ending.
vbAppTaskManager 3 The Windows Task Manager is closing the
application.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Answer from Walkenbach's "Excel 2003 Power Programming with VBA" (whom I have
no connection with, except that I learned almost everything I know from his
books):

You can't disable the Close button, but you can write a procedure for the
"QueryClose" event, which is triggered by a command to close the UserForm and
executes BEFORE the form actually closes. The UserForm_QueryClose procedure
has 2 arguments. The CloseMode argument contains a value that indicates the
cause of QueryClose being triggered. When CloseMode=vbFormControlMenu, that
means the user clicked the "X" (close) button. Just write an If statement to
test CloseMode for vbFormControlMenu, and if true set the second argument --
Cancel -- to True, which cancels the close command.

If you have any confusion with this, email me and I'll send you the whole
section from Walkenbach's book. (I've never used this particular method, so
I can't personally vouch for it.)