The Excel VBA MultiPage control is a useful way of logically grouping the controls of your userform. Especially if you have many (just think of the Format Cells window).

In this blog post, we will look at how to create and use the MultiPage Control on your Excel VBA userforms, and also how to modify its properties and refer to it during runtime.

For this example, we have a list of customers. And we want to use a userform when editing the details about these customers. This will provide greater validation and more reliable data entry than editing the details directly on the sheet.

We would also like to run common tasks from this userform. So the Excel VBA MultiPage control has been used to group the customer details, and common tasks onto separate tabs, or pages.

This image shows the customer details tab.

And this shows the Tasks tab.

So let’s have a look at how to use the Excel VBA MultiPage Control.

[Read more…] about Excel VBA MultiPage Control on Userforms

In this blog post, we will look at showing search results in a ListBox of a userform in Excel VBA.

We want a user to type into a text box, and the search results of that entry to appear in a ListBox. A search result can then be selected and added to a row on a spreadsheet.

This is the example.

We have the form below. The user enters the ID of a product, and VLOOKUP formulas return the related information from a product list into the other columns.

Sometimes though, the user does not know the product ID.

If so, they can click on the Advanced Search button. This opens the form below, where they can enter keywords to search for the product they need.

Then our VBA code will return the search results to the ListBox, and add the product they select to the form on the spreadsheet.

[Read more…] about Display Search Results in a ListBox – Excel VBA

In this blog post, we will look at how to create dependent combo boxes for your userforms in Excel VBA.

The drop down options in the second combo box are dependent upon the selection made in the first combo box.

We will cover 2 ways of achieving this.

Create a Dependent Combo Box with Named Ranges

In this example we have two combo boxes on a userform.

One with a list of countries, and another with a list of cites. We would like the second list to only show the cities for the country selected in the first list.

The first combo box is named cboCountry and the second combo box named cboCity.

The spreadsheet with the data being used by these combo boxes can be seen below.

Each list has been given a defined name. We can then reference that name within our VBA code in a similar way to my dependent data validation list tutorial.

The VBA code below has been used in the combo box change event for cboCountry, so that whenever a selection is made in that list, the code is triggered.

This code uses the Select Case conditional construct. If you are not familiar with this technique, check out my Select Case tutorial.

[Read more…] about Dependent Combo Boxes on Excel VBA Userform

When learning Excel VBA, you need to know how to use loops competently.

By using loops we can walk through a range of values, rows, sheets or other objects and perform a task repeatedly on each one.

This is a fundamental Excel VBA skill. Not being able to efficiently construct loops will heavily limit the macros you can create.

There are 2 types of loop in VBA. There are For loops and Do loops. This article focuses on learning For loops.

If you want to learn Excel VBA fast. Enrol in our online course. This is a complete beginners guide to learning Excel VBA.

[Read more…] about Excel VBA For Loops – A Beginners Guide

The Evaluate function is a hidden secret that few Excel VBA users know. In this blog post, we will explore what it does, and why you will be happy to know it.

The Excel VBA Evaluate Function converts a Microsoft Excel name to an object or a value.

The syntax for the Evaluate function looks like this;

Evaluate(Name)

The name can be a formula or the name of an object that you want to evaluate. This name must not exceed 255 characters.

What does this mean? And why is this useful?

Well let’s have a look at some typical uses of the Evaluate Function.

[Read more…] about Excel VBA Evaluate Function – How to use this Secret VBA Function