Microsoft Office Tutorials and References
In Depth Information
Changing a UserForm’s Size
Although you can’t actually disable the Close button, you can prevent the user from closing a
UserForm by clicking it. You can do so by monitoring the UserForm’s QueryClose event.
The following procedure, which is located in the code module for the UserForm, is executed
before the form is closed (that is, when the QueryClose event occurs):
Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox “Click the OK button to close the form.”
Cancel = True
End If
End Sub
The UserForm_QueryClose procedure uses two arguments. The CloseMode argument
contains a value that indicates the cause of the QueryClose event. If CloseMode is equal to
vbFormControlMenu (a built-in constant), that means that the user clicked the Close button. If
a message is displayed, the Cancel argument is set to True , and the form isn’t actually closed.
The example in this section is available on the companion CD-ROM in a file named
queryclose demo.xlsm .
Keep in mind that a user can press Ctrl+Break to break out of the macro. In this
example, pressing Ctrl+Break while the UserForm is displayed causes the UserForm to be
dismissed. To prevent this occurrence, execute the following statement prior to
displaying the UserForm:
Application.EnableCancelKey = xlDisabled
Make sure that your application is debugged before you add this statement. Otherwise,
you’ll find that it’s impossible to break out of an accidental endless loop.
Changing a UserForm’s Size
Many applications use dialog boxes that change their own size. For example, Excel’s Find and
Replace dialog box (displayed when you choose Home Editing Find & Select Replace)
increases its height when the user clicks the Options button.
The example in this section demonstrates how to get a UserForm to change its size dynamically.
Changing a dialog box’s size is done by altering the Width or Height property of the UserForm
Search JabSto ::

Custom Search