Mike,
I copied the following section from Module 4 of "Project 98 Visual Basic
Environment Training". You can download the whole training file by going
to the MVP website at:
http://www.mvps.org/project/links.htm
The link to the VBA training file is at the bottom of the page.
Preventing a UserForm from Being Dismissed with the Close Button
[Section title]
When you run a UserForm, a Close Button is added to the upper-right
corner of the UserForm Window. If you want to prevent the UserForm from
being closed with the Close Button, you must trap the QueryClose Event.
The QueryClose Event occurs just before the UserForm is unloaded from
memory. Use the CloseMode Argument of the QueryClose Event to determine
how the UserForm is being closed.
A value of vbFormControlMenu for the CloseMode Argument indicates that
the Close Button was clicked. To keep the UserForm active, set the
Cancel Argument of the QueryClose Event to True.
The following steps show you how to use the QueryClose Event to prevent
a UserForm from being dismissed with the Close Button:
1. Create a new file in Microsoft Excel, Microsoft PowerPoint, Microsoft
Project 98, or Microsoft Word.
2. On the Tools Menu, point to Macro, and then click Visual Basic Editor.
3. Insert a UserForm into your file. To do this, click UserForm on the
Insert Menu.
4. Draw a CommandButton Control on the UserForm (Drag n¹ Drop or Click
n¹ Draw).
5. Double-click the UserForm to display the Code window for the UserForm.
6. In the module, type the following code:
7. Run the UserForm.
Prevent UserForm Dismissal with Close Button Example
Clicking on a Command Button will cause a Click Event. The
CommandButton1 Click event macro unloads the UserForm from memory using
the Unload Statement with the Me Keyword.
Private Sub CommandButton1_Click()
Unload Me
End Sub
The QueryClose Event occurs if the user clicks on the UserForm¹s Close
Button or as a result of the "Unload Me" in the Click Event macro.
The UserForm QueryClose event macro tests to see if the CloseMode
Argument = vbFormControlMenu and if so, sets the Cancel Property to
True, stopping the closing process. The Caption Property is then changed.
As a result, the UserForm is not dismissed when you click the Close
Button. You must click the CommandButton Control to dismiss (unload) the
UserForm.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
IF CloseMode = vbFormControlMenu Then
Cancel = True
Me.Caption = "Click the CommandButton to close Me!"
End If
End Sub
Hope this helps.
John
