VBA UserForms are a key tool for managing user interactions. When a UserForm is well designed, it guides the user through the options and settings without any help file or guidance. From my own UserForm development, I know one of the most overlooked aspects is the closure of the UserForm itself.
This post will cover some of the key considerations to ensure the closure process achieves the desired outcome.
Basic VBA code
To start with, let’s look at the basic VBA code for opening and closing UserForms. In this post, the UserForm is called myUserForm, and it looks like this.
The button names are cmdHide and cmdUnload, and the text box is called txtTextBox.
Display a UserForm
The code to display a UserForm is usually stored outside of the UserForm itself, in a standard module. The following code will show the UserForm.
Sub OpenUserForm() myUserForm.Show End Sub
Hide a UserForm
To hide a UserForm using a button, the code can be contained with the UserForm itself, or in a separate module.
The code below shows an example where the code is contained within the UserForm. Me is used to reference the UserForm object.
Private Sub cmdHide_Click() Me.Hide End Sub
Where the code is contained within another module, we must refer to the name of the UserForm, as shown by the code below.
Sub hideMyForm() myUserForm.Hide End Sub
Unload a UserForm
Another way to close a UserForm is to unload it. Just like the examples above, the code can be referenced from within or outside the UserForm code module.
Referenced from within the UserForm module:
Private Sub cmdUnload_Click() Unload Me End Sub
Referenced from outside the UserForm module:
Sub unloadMyForm() Unload myUserForm End Sub
The simplest option to close a UserForm is the standard close button. It is convenient as it right there at the top of the window. By default, It doesn’t require any code to work as it’s part of the Windows framework.
Be aware that the close button will use the unload method when closing the UserForm.
Assigning the Esc key
The Esc key is commonly used within interface design to close a window. We can use this on our UserForm too, though the option to apply the setting is in a strange place.
Select a button on the UserForm and set the Cancel property to True.
When the UserForm is displayed, pressing the Esc key will trigger the button you applied the Cancel property to. Therefore Cancel can be assigned to a button with hide or unload.
Do you know the fastest way to learn foreign languages? It is to read, write, speak, and think in that language as often as possible. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up.
Therefore, what most people like you need is lots of examples that you can practice. That is why the 100 Excel VBA Macros eBook exists. It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers. The book contains:
- 100 example codes to practice reading and writing macros that will embed the language into your thinking.
- An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
- Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
- Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.
Hide vs Unload
As there is an option to hide or unload, which should you choose? Does it matter? YES, it definitely does matter.
The two options are different in their approach because they achieve slightly different things.
Unload will close the form completely; it will no longer exist in memory. It will be as if the initialize event has not triggered, so if we refer to any of the objects on the UserForm, they will have no value.
Hide makes a UserForm invisible, it’s still there, we just can’t see it. As it still exists, we can still reference the objects on the form to retrieve their values.
As an example, if there is text in a text box, we can still reference it after the form has closed. The code below will display a message box with the text from the UserForm after the form has been closed.
Sub hideMyFormShowMessage() myUserForm.Hide MsgBox myUserForm.txtTextBox End Sub
As the form remains open, it continues to hold the memory.
Preloading the UserForm
What if we want to reference an object on a UserForm before we display it? The initialize event has not executed and the form does not exist in memory. To get around this issue, we can use the load command to create the UserFrom, but not display it.
Sub LoadUserForm() Load myUserForm End Sub
By using this method, the initialize event will trigger, but not the activate event. The activate event will trigger only once the UserForm is displayed.
To guarantee the UserForm is always in memory, load the form during the workbook open event.
How to close using the X button
By default, the [X] close button will unload the UserForm. Which is a problem if we want to hide it. But we can hijack the [X] button control to achieve the same as the hide command.
The UserForm has an event called QueryClose. Using this event, we can cancel the unload action, then hide the UserForm.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True Me.Hide End If End Sub
Once you’ve canceled the unload action, you could now refer to the elements on the UserForm, just like the normal hide.
How did the user close the form?
As there are multiple ways to close a UserForm, it may be useful to know which option the user selected. Did they click “OK” or “Cancel”? In this situation, it is easier to use a public variable. Assign the value to the variable as part of the close procedure.
The public variable can then be accessed by any module.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: