Once the UserForm layout has been completed, you must configure its settings so that the options that the user has in each control have functionality. To do this, we will use the events of the UserForm.
In this way we can:
To customize the various controls (E.g. combo boxes) from the beginning of using UserForm we will use the Initialize event. This event executes the Sub code every time the UserForm is loaded into Excel memory (for example when it enters the view with or ).
To create a startup event:
If there is no Sub in the Code Window, manually add the event described in the code below:
Private Sub UserForm_Initialize() End Sub
The VBA may have created the event Private SubUserForm_Click() automatically, delete it if you want (in this case we are setting Initialize and not Click).
By default the Subs created automatically through the VBE menus are Private. Change to Public Sub if you want to access them not only by UserForm.
Make sure that the name of the controls used in the Initialize event are the same as those created for the UserForm layout. If so, we can add the following code (to add functionality to the previous example of Userform Layout):
Private Sub UserForm_Initialize() 'Clears the text box TextBox1.Value = "" 'Fills in the initial age TextBox2.Value = 30 'Sets the initial age SpinButton1.Value = 30 'Clears the option buttons OptionButton1.Value = False OptionButton2.Value = False OptionButton3.Value = False 'Clears the combo box ComboBox1.Clear 'Fills in the combo box With ComboBox1 .AddItem "Purchasing Department" .AddItem "Finance Department" .AddItem "Legal Department" .AddItem "Planning Department" .AddItem "Human Resources Department" .AddItem "IT Department" .AddItem "Sales Department" .Style = 2 'Prevents the user from entering an input that is not on the list End With 'Clears the list box ListBox1.Clear 'Populate the list box With ListBox1 .AddItem "Downtown" .AddItem "Historic District" .AddItem "Chinatown" End With 'Clears the check boxes CheckBox1.Value = False CheckBox2.Value = False End Sub
The initialize event - UserForm_Initialize() - occurs when the UserForm is loaded into the memory, and the activation event - UserForm_Activate() - occurs when the UserForm is displayed. Therefore, the initialize event happens earlier than the activation event.
Before the UserForm can be displayed to the user it must be loaded into the memory. You can load and display it at the same time or load it in advance and then display it.
Sometimes, when working with very complex UserForms it may be advantageous to load it into the memory in advance. In this way the form will be displayed more quickly when called by the user.
If the UserForm is already loaded in the memory (Load), only the activation event will be triggered when it is called (Show).
|Display the UserForm||UserForm1.Show||Loads and displays the UserForm to the user|
|Load the UserForm||Load UserForm1||Loads the UserForm into memory (but does not display it to the user)|
|Hide the UserForm||UserForm1.Hide||Hides the UserForm but does not clear it from memory|
|Close the UserForm||Unload UserForm1||Closes the UserForm and clears it from memory|
If the UserForm is already loaded in memory (Load), only the activation event will be triggered when it is called (.Show).
Both Load UserForm1 and UserForm1.Show should be used in common modules (), distinct from the UserForm code.
To display the UserForm you need to run a macro like the following:
Sub ShowForm() UserForm1.Show End Sub
This macro, inserted in a , can be assigned to a button in a worksheet, or run in response to a user triggered event.
Note that after setting the Initialize event, if the UserForm is called (Show) the button options will already be available:
Although the initial control options have been configured, still more settings are required to the operation of all functionalities of the form (E.g.: command buttons, spin buttons, etc.).
When executing the Show command and displaying the UserForm, the worksheet is usually locked for editing, however we can change it with parameterization.
Sub ShowForm() UserForm1.Show 0 'vbModeless End Sub
|vbModal||1||UserForm is a modal window. Default option.|
|vbModeless||0||UserForm is not a modal window|
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2023 SuperExcelVBA | ABOUT