UserForm is a class module with a designer and a default instance. The designer can be accessed by pressing Shift+F7 while viewing the code-behind, and the code-behind can be accessed by pressing F7 while viewing the designer.
Being a class module, a form is therefore a blueprint for an object. Because a form can hold state and data, it's a better practice to work with a new instance of the class, rather than with the default/global one:
With New UserForm1 .Show vbModal If Not .IsCancelled Then '... End If End With
UserForm1.Show vbModal If Not UserForm1.IsCancelled Then '... End If
Working with the default instance can lead to subtle bugs when the form is closed with the red "X" button and/or when
Unload Me is used in the code-behind.
A form should be concerned with nothing but presentation: a button
Click handler that connects to a database and runs a parameterized query based on user input, is doing too many things.
Instead, implement the applicative logic in the code that's responsible for displaying the form, or even better, in dedicated modules and procedures.
Write the code in such a way that the UserForm is only ever responsible for knowing how to display and collect data: where the data comes from, or what happens with the data afterwards, is none of its concern.
Make a well-defined model for the form to work with, either in its own dedicated class module, or encapsulated within the form's code-behind itself - expose the model with
Property Get procedures, and have the client code work with these: this makes the form an abstraction over controls and their nitty-gritty details, exposing only the relevant data to the client code.
This means code that looks like this:
With New UserForm1 .Show vbModal If Not .IsCancelled Then MsgBox .Message, vbInformation End If End With
Instead of this:
With New UserForm1 .Show vbModal If Not .IsCancelled Then MsgBox .txtMessage.Text, vbInformation End If End With
Forms typically have a Close button, and prompts/dialogs have Ok and Cancel buttons; the user may close the form using the form's control box (the red "X" button), which destroys the form instance by default (another good reason to work with a new instance every time).
With New UserForm1 .Show vbModal If Not .IsCancelled Then 'if QueryClose isn't handled, this can raise a runtime error. '... End With End With
The simplest way to handle the
QueryClose event is to set the
Cancel parameter to
True, and then to hide the form instead of closing it:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Cancel = True Me.Hide End Sub
That way the "X" button will never destroy the instance, and the caller can safely access all the public members.
The code that creates an object should be responsible for destroying it: it's not the form's responsibility to unload and terminate itself.
Unload Me in a form's code-behind. Call
Me.Hide instead, so that the calling code can still use the object it created when the form closes.
Use the properties toolwindow (F4) to carefully name each control on a form. The name of a control is used in the code-behind, so unless you're using a refactoring tool that can handle this, renaming a control will break the code - so it's much easier to do things right in the first place, than try to puzzle out exactly which of the 20 textboxes
TextBox12 stands for.
Traditionally, UserForm controls are named with Hungarian-style prefixes:
Labelcontrol that indicates a user name.
TextBoxcontrol where the user can enter a user name.
ComboBoxcontrol where the user can enter or pick a user name.
ListBoxcontrol where the user can pick a user name.
Buttoncontrol labelled "Ok".
The problem is that when e.g. the UI gets redesigned and a
ComboBox changes to a
ListBox, the name needs to change to reflect the new control type: it's better to name controls for what they represent, rather than after their control type - to decouple the code from the UI as much as possible.
UserNameLabelfor a read-only label that indicates a user name.
UserNameInputfor a control where the user can enter or pick a user name.
OkButtonfor a command button labelled "Ok".
Whichever style is chosen, anything is better than leaving all controls their default names. Consistency in naming style is ideal, too.