Closing VBA UserForms: Hide vs Unload

Closing VBA Form Hide vs Unhide

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.

myUserForm

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

Close button

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.

Close Button

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.

Set Cancel Key

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.

100 Excel VBA Macros

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.

100 Excel Macros Book

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

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

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.

Close Button

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.

Email Address * First Name *

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.


Don’t forget:

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

10 thoughts on “Closing VBA UserForms: Hide vs Unload

  1. Sam Conley says:

    Thank you for this info. My problem seems to be unique as I do not find it addressed anywhere. When I close User Form (No matter how I close it) control does not return to the worksheet. I can select a worksheet, select a cell, but the user cannot type in the cell or move via arrow keys or do anything at all involving keyboard. He has to click on worksheet, and then all keys work find. Is there any way to eliminate that step?? Can we close User Form and then start typing on spreadsheet without having to click it??

    Thank you for any assistance.

    Sam

    • Excel Off The Grid says:

      Sam – I’ve tested this out on 4 different user forms and they all seem to revert control back to the active cell when the form is closed. I’ve not been able to replicate your issue. The default behavior appears to be as per your desired outcome, which may be why it’s not addressed anywhere.

      Which version of Excel and Windows are you using? It may be a specific issue to those versions.

  2. Gainluca Barile says:

    Same problem, ..try use this instruction code after unload or hide forms:

    End

    This will stop all macros that are working in background.

  3. Doug says:

    Sam, I think I’ve noticed the same problem. I’m seeing it in the development environment, where I’m trying to F8 step through code to see what happens with my form, which events occur and so on. Because I’m new to Userforms, I have been assuming it’s just me not knowing what’s going on, but it’s like I can never get control back until I go to the menu bar and use Run/Reset. I have concluded that Userforms doesn’t work quite the way ordinary VBA modules work in the development environment. (For instance, having to right-click in Project Explorer to swap from Code view to Object view to me seems a bit weird and clunky. And why does the userform just pop up on top of a worksheet window? What if you just want a form and not a worksheet?) Anyway, good luck.
    I reckon that if I just persist I’ll just discover by trial and error what works and what doesn’t.

  4. Tonye says:

    Hello Sam,

    Try this code at the end of the “UserForm_QueryClose” sub:

    ThisWorkbook.Sheets(“SheetName”).Range(“Cell Ref”).Select

    This would return controls to the worksheet and would allow you to start typing and also move around via the arrow keys once the userform closes.

    See example below:
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = vbFormControlMenu Then

    Cancel = True
    Me.Hide

    End If

    ThisWorkbook.Sheets(“SheetName”).Range(“Cell Ref”).Select

    End Sub

  5. Melvyn Shepherd says:

    Hi, I am new to developing forms and using vba in Excel.
    This is an interesting article which I found useful is my development of user forms.

    I do have query, that you could answer, if you would?
    I have several sheets, each containing a different table. If I keep these sheets always hidden from the users, does this effect the processing of my forms and their associated vba code?

    • Excel Off The Grid says:

      Hi Malvyn – it partly depends on how you’ve written the VBA code. It shouldn’t cause any issues at all if you’ve written efficient code. But, if you’re new to VBA then you may have used a lot of unnecessary Select / Activate commands which refer to the hidden sheets, that would result in errors.

      Either way, with a bit of effort, you can ensure that it’s not a problem 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *