Creating VBA Userforms
In this Article
- VBA Userforms
- Built in VBA Userforms
- Message Box
- Input Box
- Get Open Filename
- Excel Default Dialogs
- Inserting a New User Form
- Using the Toolbox
- Adding an Exit Button to Your Form
- Adding a Label Control to a Form
- Adding a Text Control to the Form
- Initialize and Activate Events on a Form
- Saving Your Application and Forms
- Modal and Non-Modal Forms
- Closing a Form
- Enabling and Disabling Controls
The Userform is a very important part of programming in VBA. It allows you to build a professional looking user interface to communicate with the users of your VBA application. It also allows you to totally control the user in what they are doing to your workbook.
You can, of course, use worksheet cells to accept parameters from the user, but the userform produces a far better user experience.
By expanding the height and width of your userform to the size of the Excel window, you can make your application look like a normal Windows application, with the user being totally unaware that they are using Excel as the host.
All the normal Windows controls, such as drop downs, list boxes, tick boxes are available to you. You also have a huge range of methods, events and properties to use to enhance the user experience.
An important point is that when you display a userform that is built in or is modal, you cannot edit your code in the VBE nor access any Excel functionality. Only when the form is closed will the cursor appear in your code.
Built in VBA Userforms
Excel VBA as several built-in forms that can be used to communicate with the user.
This is the most commonly used form in VBA. It simply displays a text message, possibly informing a user that they have entered invalid input or that a VBA process has finished running. In their simplest form, they display a text string, but you can also add an icon such as a question or exclamation mark, and give the message box a different title.
This is a basic example. There is only one button to click, and the title bar says ‘Microsoft Excel’
The code to produce this is very straightforward:
Sub TestMsgBox() MsgBox "This process has completed" End Sub
You can use different parameters to add buttons, icons, and change the title bar
Sub TestMsgBox() Dim Ret As Variant Ret = MsgBox("Are you sure?", vbYesNo Or vbQuestion, "My Application") If Ret = vbYes Then 'Your process here Else Exit Sub End If End Sub
This code adds in a ‘Yes’ and ‘No’ button and a question mark icon, and sets the title bar. Note that you can combine the styles of the message box by using the ‘Or’ operator
Also, when you are returning a value from a message box, the return variable must be defined as a variant or vbMsgBoxResult, and the message box statement must use brackets,
There is a very simple input box built into VBA, although it is quite restrictive in what you can do with it. If you can, it is better to design a custom userform
Sub TestInputBox() Dim Ret As String Ret = InputBox("Please enter your name", "Enter Name") MsgBox Ret End Sub
You can also add a default value for the input into the parameters.
Get Open Filename
This allows you to utilise the Windows file dialog within your VBA code. It looks very impressive to the user when it is running, but it is very simple to incorporate and you automatically get all the file dialog functionality with it.
The code restricts the user to only see Excel files. Unfortunately, they could type in a non-Excel filename into the File name box, and click the open button, so you would need some code to ensure that an Excel file has been selected.
Use the ‘ChDir’ command to change the default directory to your own requirements before displaying the file dialog
Note the use of wildcards in the FileFilter parameter. The Excel files to display could be pre 2007, have macros, or be binary so the filter is ‘.xls*’.
Sub TestFileDialog() Dim MyFile As String ChDir "C:\temp" MyFile = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*", , "Select a file") MsgBox MyFile End Sub
If required, you can allow the user to select several files at once by using the MultiSelect parameter. The default is False (single select only)
Sub TestFileDialog() Dim MyFile As Variant ChDir "C:\temp" MyFile = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*", , "Select a file", , True) For Each f In MyFile MsgBox f Next f End Sub
The user holds down the Shift key in the file dialog to select multiple files.
The For Each loop displays the full path and name of each file selected
Excel Default Dialogs
Within Excel VBA, there is a Dialogs collection which you can use to display any standard Excel dialog. The downside is that you cannot access the parameters that the user has chosen or change the appearance of the dialog, but these dialogs can be useful in directing the user to a standard Excel function, and allowing them to choose specific parameters within the dialog.
A good example of this is to display the ‘Print’ dialog from VBA:
Sub TestPrintDialog() Application.Dialogs(xlDialogPrint).Show End Sub
When you open the brackets in the Dialogs collection, you will see a list of a huge number of constants for built-in dialogs. It is worth experimenting with some of these options within your code
Inserting a New User Form
You can design your own custom user forms by inserting a userform into the Visual Basic Editor (VBE)
You do this by selecting Insert | UserForm on the VBE menu bar.
Click on ‘UserForm’ and a new blank form will appear ready for you to develop on
The actual form itself (shown as ‘UserForm1’) looks quite small, but you can use the handles around it to enlarge or make it even smaller by dragging the handles with your cursor.
There is a properties window in the bottom left-hand corner of the screen. As the focus is directly on the form itself, this contains all the properties for the form specific to that form.
Note that when you start adding controls such as combo boxes and command buttons, these controls all have their own set of properties, and they can be very different in what you can do with each of them.
The properties that you see currently only apply only to the form itself.
The ‘Name’ property is the name used to define your form object within the VBA code. You may want to use something more meaningful for the object name, so that when you are reviewing your code, it is obvious which form is being used.
The ‘Name’ property will also reflect through to the ‘Project Explorer’ window in the top left-hand corner of the screen
You will want to alter the title bar of your form to something different from ‘UserForm1’, and you can do this by typing in your new text at the ‘Caption’ property
You can make a huge number of changes to how your form is seen by the user. You can alter colours, add images e.g. a corporate logo, change the position using ‘Left’ and ‘Top’, change the size using ‘Height’ and ‘Width’, change the mouse pointer, and many more
All of these properties can also be changed programmatically for any control that you have added to your form. For example, a user may select from a list box control, and you may want to disable or hide other controls based on the user’s choice
Using the Toolbox
You will notice that when you click on the form itself, a toolbox pop-up appears. If you click anywhere else e.g. the properties pane, it will disappear, but re-appear when your click on the form.
The toolbox provides the real mechanics of the form design. This enables you to add the normal Windows controls that users are familiar with to your form.
You will have notice that on the form, there is a network of dots. This is a ‘snapgrid’ so that when you add a control to the form, it will automatically align the position to the rows and columns of dots. This helps enormously with aligning your controls so that your do not get a ragged appearance of the controls
Should you click on a control, and then decide not to use it, clicking on the ‘Arrow’ icon in the top-left corner of the toolbox will change your cursor back to normal.
You can add additional controls to the toolbox by using Tools | Additional Controls on the VBE menu. There are a considerable number of these available, but depending on you Windows and Excel versions, they do not always work so some experimentation is often required.
Also, your users may not have access to some of these additional controls or be running older versions of Windows and Excel, which may cause problems. In large organisations, especially if they are global, there is no such thing as a standard build PC that you can rely on!
Adding an Exit Button to Your Form
A Command button is simple to add to the form. This looks the same way as buttons that you see in other Windows forms, usually as an ‘OK’ or ‘Cancel’ button.
Click on the Command Button icon in the toolbox. This is the second icon from the left on the bottom row of icons. See the image above. It has the letters ‘ab’ on it.
You can either hold your mouse button down and drag the control onto your form, or you can move the cursor to the form, where it will change to a ‘cross’ cursor and you can position and size your button
If you drag the control to the form, you will get the default sizing of the button. Moving the cursor to the form allows you to change the size of the button by dragging the ‘cross’ cursor across your form
Your form will now look like this:
The button will have default text as the caption, but you will want to change it to your own requirements. You can click on the text within the button (‘CommandButton1’) and this will allow you to edit the caption directly.
You can also change it in the properties window (bottom left-hand corner of screen). You will see a property called ‘Caption’ and you can edit the value for this. Change this to ‘Exit’
As with the form properties, the ‘Name’ property defines the name that will be used in your VBA code. You may want to use a name that is more meaningful and obvious within your code. You can enter this against the ‘Name’ property.
You can re-position the button by dragging it about on the form, and you can resize it by clicking on the button handles (white square boxes) and dragging the handles to make it larger or smaller
You can also re-size the button by changing the Height and Width values in the properties window
You can view your form in Excel by clicking on the green triangle in the VBE toolbar, or pressing F5
You can call your form from VBA code within a module by using the ‘Show’ method
Sub ShowForm() UserForm1.Show End Sub
Your user form is effectively a global object and can be called from anywhere within your code
Currently, your command button does nothing because there is no VBA code behind it. You have to write this yourself! All that can happen at the moment is that you can click on the ‘Close’ X in the top right-hand corner of your form.
To add VBA code, double click on the button on the form
This will take you to the normal VBA code window, and will show the default event of click.
You use the ‘Hide’ method to close the form, and you can also add in any other code, such as a message box to confirm to the user what has happened.
Note that the code window has two drop downs at the top. The first one allows you to select your form controls, and the second shows all the events that are available for you to add code. The obvious one for a button is the ‘Click’ event, but there are others such as ‘Double Click’ or ‘Mouse Move’
When you run your form now, the button actually does something. The form vanishes and a message box is displayed confirming that the form is closed
You can, of course, enlarge the exit code. You may wish to display another form, or take action on parameters that the user has entered on your form
Adding a Label Control to a Form
Label controls are for prompting the user as to what sort of data they need to input to a control on the form e.g. text box, drop down, etc. A label has no borders by default, but these can be added through the properties window if required.
As a control, they are read only to the user and are simply a way of putting text on the form, whether it is a bold heading, or an instruction of what to enter or choose.
To add a label, click on the ‘A’ icon in the toolbox (top row, second from left) and either double click on it or move your cursor to the form and select position and size.
Using the ‘Caption’ property in the properties window, or clicking on the label control, you can enter the text for the label control.
Note that the text will wrap according to the size of the label control, and if the string of text is too long, it will not appear completely on the form, so you need to be careful of the sizing of the label control.
Using the properties window, you can change the appearance of the label control, with different colours, fonts, back style e.g. if it overlays an image and you want it to be transparent
No code needs to be created for a label control. The main purpose is to add text to the form so that the user can see how all the other controls work
Adding a Text Control to the Form
A text control is used to allow the user to input text e.g entering a name or comments
The text control is added from the toolbox by clicking on the text control icon (top row, third from left) and double clicking or dragging the control into position on your form.
The text control is often confused with the label control, but the text control is the one for user input
The ‘Enter your name’ text is a label control, as previously described, and we now have a white text box ready for the user to type something into
Using the properties window, you can change the colours, fonts, special effects, or use password characters for your text box. Enormous flexibility is available
One very important property for a text box is the ‘MultiLine’ property. If you want the user to enter a large amount of text into the text control e.g. comments, then the ‘MultiLine’ property must be set to True.
It is a default of False which means that however big you make your text box, the text entered will stay on one continuous line and will scroll out of the text box. It will not wrap around within the box.
There is no pop-up when you right click on your text box when it is running, but CTRL+V will work for Paste, and CTRL+C will work for Cut, should the user want to cut and paste text to and from other applications
Again, you have to write your own code to deal with text that the user has typed in. You may wish to transfer it to a cell in a worksheet
You can add this code into the ‘Change’ event for the text box
Private Sub TextBox1_Change() Sheets("Sheet1").Range("A1").Value = TextBox1.Value End Sub
You may also want to put some validation code in to check that the user is not entering rubbish which will have disastrous effects on your application
The change event is no good for this because it is called every time the user types in a new character. The user could start typing a string of text and instantly find that they have broken your validation rules before they have completed a valid text.
You use the ‘Exit’ event. This is triggered when the user moves the focus to another control on the form, which means that the user is no longer entering data.
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If IsNull(TextBox1.Value) Or Len(TextBox1.Value) < 4 Then MsgBox "Name is invalid", vbCritical TextBox1.SetFocus End If End Sub
When the user clicks on another control on the form, this code tests for either a null value in the text box, or less than 4 characters. If the test is true, then a message box with a critical icon appears to inform the user that the name is invalid, and the focus is moved back to the offending text box for the user to correct.
Note that even if the user clicks on the Exit button, the text box exit event will be performed first, so this prevents the user exiting without correcting the input
Initialize and Activate Events on a Form
When VBA first creates and builds a form it triggers an ‘Initialize’ event. However, because the form is also displayed at this point, it also triggers an ‘Activate’ event. From then onwards, every time the form appears using the ‘Show’ method or it appears as part of a hierarchy of forms, then the ‘Activate’ event is triggered, but not the ‘Initialize’ event
The ‘Initialize’ event only happens once, but the ‘Activate’ event can happen many times
On your form you may want to set up default values from the worksheet in the input controls e.g. text boxes, so that these appear on the first use of the form, but the user can overwrite the defaults and these new values will remain in place so long as the code is running
Private Sub UserForm_Initialize() TextBox1.Value = Sheets("Sheet1").Range("A1").Value If TextBox1.Value=”” Then TextBox1.Visible=False Else TextBox1.Visible=True End If End Sub
You can find the ‘Initialize’ event in the second drop down in the code window, and the userform name in the first drop down.
This code will use the value at cell A1 on ‘Sheet1’ as the default value in the text box created earlier in this article. When the form appears for the first time, the default value will appear. The user can then overwrite the default value and this will be retained. If CellA1 is blank the text box will be hidden otherwise is will be visible
The default value could also be hard coded:
TextBox1.Value = “John Smith”
You may also want to make sure that the values that the user has entered re-appear whenever the user fires up that form within that particular Excel session. VBA code can easily write the values back to cells within the workbook using the ‘Exit’ event on a control, and re-instate them using the ‘Activate’ event on the form
Private Sub TextBox1_Exit(ByVal Cancel as MSForms.ReturnBoolean) Sheets("Sheet1").Range("A10").Value = TextBox1.Value End Sub
Private Sub UserForm_Activate() TextBox1.Value = Sheets("Sheet1").Range("A10").Value End Sub
This code will make the user’s values persistent and also ensure that they are saved off with the rest of the workbook
Saving Your Application and Forms
When you save your Excel workbook containing your forms, all the forms and their VBA code get saved as well. However, any values that the forms hold whilst they are displayed will be lost.
It is important to write code so that when the user exits the workbook, or the form, the values are written back to cells within the workbook and so are preserved.
Modal and Non-Modal Forms
The form itself has a property ‘Show Modal’. This is set by default to True, but it can be changed to False (non-modal)
If a form is modal, it means that none of the Excel functionality can be accessed while the form is being displayed. This includes your code in the VBE window. You can view the code, but the cursor and keyboard are disabled.
In a non-modal form, you can access all the Excel functionality, including the VBE window, whilst the form is being displayed.
This is important from the point of view of controlling user behaviour
Closing a Form
However well you write your code to force the user down a certain route, they can easily circumvent it by clicking on the ‘Close’ X in the top right-hand corner of the form
You can prevent this happening by modifying the ‘QueryClose’ event of the form
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Cancel = True MsgBox "This action is disabled" End Sub
The ‘QueryClose’ event is triggered when the user clicks the ‘Close’ X of the form. This code cancels the action, so the user is forced to use your ‘Exit’ button and the code that you have behind it.
Enabling and Disabling Controls
All controls on your form have a property called ‘Enabled’ which is set to True or False. If it is False, then the control is greyed out. It can be seen but cannot be used.
There is also a property called ‘Visible’ which again is set to True or False.
You can write code to either make a certain control unusable, or to make it totally invisible to the user. Using an ‘If’ statement, you can choose the circumstances when you need to do this
For example, you could disable the ‘Exit’ button initially, until the user has entered a value into the TextBox1 (name)
Private Sub UserForm_Initialize() CommandButton1.Enabled = False End Sub
Private Sub TextBox1_Change() If Len(TextBox1.Value) > 0 Then CommandButton1.Enabled = True Else CommandButton1.Enabled = False End If End Sub
This code uses the form ‘Initialize’ event to disable the exit button (Command Button 1) when the form first appears and then uses the ‘Change’ event on TextBox1 (name) to enable the Exit button if something has been typed in or disable it if the box is blank.
The ‘Change’ event is triggered every time a new character is typed in or deleted from the text box. If the user tries to enter text to make the button enabled, and then deletes all the text, the button will instantly be disabled