Excel Zoom

...because it's more than just a calculator


Using Userform and VBA to get Dynamic Data Validation

by Leave a Comment

Lists are one the commonly used features of Excel. We use them to choose amongst the already selected choices and to avoid repetitive typing. One of the methods to do this is to use Dropdown menu from the Data Validation Option under Data Tab.

Though this is realistic and practical option it lacks one feature that I really need – auto completion. If it has been present to some extent, the ability to complete the text more dynamically is not present. In today’s post we will learn how to do it with VBA Features.

Let’s start with an example:

Our List:

Let’s assume a list of suppliers that you have – it could be any list but just for the sake of example we are using a list of suppliers to produce invoices. There could be several suppliers with similar names, so we want a list that suggest me the available options.

Starting with VBA:

In order to access the VBA section of your work sheet – first save it as macro enabled worksheet to make sure that what ever you do, it can be saved in appropriate format worksheet. Otherwise, when you will press save, it will ask you to save it in this format.

Now the VBA section can be reached by either pressing Alt+F11 or by Right Click the Sheet Tab>Select View Code. Either way will lead you to the VBA Window. As a first step, select This workbook (for our example sheet named 06 – Using VBA to create ….)

 Inserting a User form:

Go to Insert>User Form and insert a user form. After inserting the form:

  • Resize it to suit your taste and need.
  • Change the caption of the form by going to Project Properties > Caption> enter Supplier Name List.
  • Add a combo box to the User form:
    • Make From Toolbars visible from View > Toolbox
    • Select Combo box and draw one in the form.
    • Similarly selection buttons and adds two to the form. Name them “Insert” and “Cancel”

The final layout of your User form should look like following:

Adding Code to User From Items:

 The next step is to insert the vba codes to the three items in the form to make then operational. We insert this code by selecting and double clicking them with mouse.

Adding Code to Combo box:

First, we select the combo box and double click it to insert the code. We need to initialize the user form and element or items to the dropdown of the combo. We need to add following lines of code:

Sub UserForm_Initialize()
With ComboBox1
.AddItem "A TO ZEE ENGINEERING"
.AddItem "A&N ENTERPRISES"
End With
End Sub

Initialization is in event. In Excel event is like an action – clicking house, pressing enter button etc so when the form is loaded, the above code adds items to the combo box. AddItem method adds items to the combo box.
Once done you should be able to see the items in the user form. To test with, load the user form by pressing F5 and see if items are added to it.

Adding Code to Insert and Cancel Button

For Insert button, add these lines of code – it will insert the selected supplier in the current cell.

Private Sub CommandButton1_Click()
Selection.Value = UserForm1.ComboBox1.Value
ActiveSheet.Select
End Sub

For Cancel button, use these lines:

Private Sub CommandButton2_Click()
Unload Me
End Sub

This will eventually unload the form. In order to use Esc button for cancelling and unloading the form, set Cancel Property of this button to TRUE.

Making all these programs work:

We have set how the form’s combo box, Insert and Cancel button will work. But one thing is still left to be set – i.e. how to call the form for use.

This will eventually unload the form. In order to use Esc button for cancelling and unloading the form, set Cancel Property of this button to TRUE.

We need to insert another piece of code to call the form. First, insert a Module from the VBA’s Insert menu. Now you can use the following code for this purpose:

Sub FormLoad()
Load UserForm1
UserForm1.Show vbModeless
End Sub

Note that form should be named UserForm1 otherwise it will not be loaded.

Setting Shortcut Keys:

Since we are done with the vba part, we can set the shortcut keys now. Close all the windows including that of vba. Go to Developers Tab>Macros> and select the Macro named FormLoad>Click Option and it will ask your for shortcut key and the description of the macro. The final output should look like:

For this worksheet, the shortcut key is Ctrl+L

Conclusion:

Now you can open any worksheet, just press the short cut key and the user from will be displayed to insert the Supplier Name in the current cell. Once inserted, you can either press Escape key or cancel to hide the form.
Please download the sample file with all the codes and supplier list from here.