Userform Methods

As explained earlier, the Userform is an object and has properties, methods, and events. In this page we see the methods of the Userform in Excel. And, methods are actions that an object can perform – you can read more about methods and the object model in this other page. Userform methods are used to show the form, hide it or close it, etc. However, in comparison with other objects, there are not many methods available and few are really useful. Many of the actions that a Userform performs (and Userform controls) are rather events (Click, Initialize, Key- and Mouse-related events, AddControl, etc). We will see that in the next section though.

Showing, hiding, and closing a Userform

We open/show a Userform typically from some procedure in a standard module using the Show method of the Userform. The picture below shows a Sub procedure (ShowForm) in Module1 that shows UserForm1 when triggered.

 

The Userform appears in the center of the screen unless otherwise specified with the relevant properties (Left and Top). We can now work with the Userform, see the information provided or use any of the functionality (via Userform controls added – see later in this training), and at some point, we may want to hide or close the form to continue working on the worksheet or closing the file. There is a slight different between hiding and closing a Userform. The method Hide is used to hide the form, but it keeps it “alive” in the background so to speak (it keeps it in memory), which means that it does not clear any data added or modified in the form, while releasing code execution and allowing to work again with the Excel application. 

 Sub HideUserform()
     UserForm1.Hide
 End Sub

Hiding the Userform is usually triggered from a button in the form itself, but can also be triggered from any other button in the workbook if Userform is modeless (property ShowModal is False - see next). See below a very simple example to understand all this. Despite we have not covered Userform controls yet, the picture below shows a Userform with a text box and a button. The button calls the macro above (HideUserform) when clicked.

  

Hiding the form will keep the data in the text box, so if we show it again, we will still see the text added there. However, if we close the form, the data is gone (is removed from memory). We can close the form manually clicking on the red cross in the upper-right corner of the Userform or we can close a Userform programmatically with the statement “Unload”. Note that this is not a method of the Userform, is a VBA statement used to remove an object from memory.

 Sub CloseUserform()
     Unload UserForm1
 End Sub

 

Closing the Userform is often triggered via a button click event (see later in the training). The event procedure would call the CloseUserform  macro above. If the code resides in the Userform module, we can simply write “Unload Me”. For example, if we had another button added to the Userform to close it, the following code associated with that button (CommandButton2) would be in the Userform module.  

 

 Private Sub CommandButton2_Click()
     Unload Me
 End Sub

 

Keep in mind that sometimes is better to first hide the form, next get the data added or modified within  the form in your main macro, and finally, after saving whatever we wanted to get from the Userform, close (unload) the Userform from within the main macro (generally in a standard module outside the Userform module). We can also do all that without even hiding the form just using Unload as a last step in the main macro in a standard module (not in the Userform module as indicated just above).

 

Code execution and ShowModal

It is important to understand that code execution stops when showing the Userform (if modal). That means that whatever comes next in the calling macro, it will not execute until the Userform is closed (or hidden). For example, the macro below shows the form first, and does not move to the next line of code until the Userform is closed – the message box with “All done” will not be displayed.

 Sub ShowUserform()
     UserForm1.Show
 
     'do other things
     MsgBox "All done"
 
 End Sub

 

However, that can be changed by setting ShowModal to False (Userform modeless). ShowModal is not a method, is a property of the Userform, but it can also be specified as a parameter of the Userform Show method. We can do that by simply adding vbModal (or the value 1) or vbModeless (or the value 0) after Show. The following line opens the Userform as modeless.

 UserForm1.Show 0

 

When a Userform is modeless, we can have the Userform open while working at the same time in the worksheet. Also, code execution in the calling macro continues. In the previous example, the macro would open the Userform and right after display the message “All done”.

The Userform is modal by default (ShowModal is True). Setting the Userform to modeless (ShowModal = False) allows to keep writing data to the worksheet, navigating the sheets, adding shapes, and using any other functionality in Excel.  For example, we could keep navigating various sheets to seek information that we need to add in the Userform (see picture below).

As we have seen in the previous chapter, Userform properties can be changed at design time in the properties window or at run time. Thus, we can set the value for ShowModal as True or False in the properties window or with code as shown below.

 UserForm1.ShowModal = False

 

 <<< Previous | Next >>>