Microsoft Office Tutorials and References
In Depth Information
Modal Versus Modeless
If you’re working in a UserForm module and you forget the names of controls,
and you’ve selected the Require Variable Declaration option (on the Editor tab
when you click Tools ➪ Options in the VBE) type Me followed by a dot. You’ll
see a list of all the methods and properties for the UserForm, including the list of
control names belonging to the UserForm.
ModAl VErsus ModElEss
Beginning with Excel version 2000, UserForms became equipped with a new property called
ShowModal . When a UserForm’s ShowModal property is set to True, that is, when it is shown as
Modal, it means that while the UserForm is visible, you cannot select a worksheet cell, or another
worksheet tab, or any of the Ribbon or menu icons until you close the UserForm. Most of the time,
this is what you will want — for the UserForm to command all focus and attention while it is visible.
At times your project will benefit from the ability to select cells and generally to navigate
worksheets while a UserForm is visible. When that’s what you need, call the UserForm by specifying the
ShowModal property as False, for example:
The preceding code line can also be written as UserForm1.Show 0 . The default setting for the
ShowModal property is vbModal (or the numeral 1), which you don’t need to specify when calling a
UserForm if you want it to be Modal. The code line UserForm1.Show vbModal , or UserForm1.Show 1 ,
or (which you have typically been using all along) UserForm1.Show will show the UserForm as Modal.
Here’s a neat trick that might interest you. When you call a UserForm as
Modeless, the UserForm will be the active object and an extra mouse click is
required to actually activate the worksheet. If you want the worksheet itself to
be the active object without manual intervention, add the line AppActivate
(“Microsoft Excel”) below the Show line; here is a full macro example:
AppActivate (“Microsoft Excel”)
disABling THE usErforM’s closE BuTTon
Some of your UserForms might require input before the user can proceed further. To enforce user
input, you can disable the Close button, usually located at the far right of the UserForm’s title bar.
This is not an everyday happenstance but when your project requires input at a critical point in a
process, you will need a way to keep the UserForm active until the required information is input.