Sort Sheet Tabs Alphabetically – Excel Macro

In this blog post we will create a macro to sort sheet tabs alphabetically with the click of a button.

Unfortunately Excel has no feature built-in to sort sheet tabs. But we can create our own, and with VBA set it up however we like.

This macro will be quite simple, but if you are not very familiar with Excel VBA you can sign up for the online Excel VBA course for beginners. This will help to get a better grasp of some of the techniques involved.

Watch the video below to see the macro being written and then tested, or read on to get the code and a written description.

Watch the Video


Continue reading

Excel VBA: Using the MultiPage Control in your Userforms

The 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 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.

MultiPage Control on a userform

Continue reading

Display Search Results in a ListBox – Excel VBA

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.

Master VLOOKUP with the ultimate VLOOKUP guide

Product order form

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.

ListBox displaying search results from a text box
Continue reading

Dependent Combo Boxes on Excel VBA Userform

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

So 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.

Two combo boxes on a userform

Continue reading

Excel VBA For Loops – A Beginners Guide

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.

Continue reading

6 Ways to Speed up your Excel Macros

Creating macros in Excel is a fantastic way to automate Excel processes and save loads of time. However as your macros get bigger, you may need to increase the speed that they execute.

This blog post looks at 6 ways that you can speed up your macros. You really need to know this stuff to create efficient macros in Excel.

If you are a user of Excel VBA already, then you may know all, or most of these. Please feel free to comment with the techniques that you use.

Watch the Video


Continue reading

Excel VBA: Select Case Statement with Examples

The Select Case statement can offer a neater and more concise alternative to nested If Then Else statements in Excel VBA.

So when you are performing complex logical tests, the Select Case statement can break it down into more logical chunks of code. This then makes it easier to write, read and understand what the code does, which everyone will appreciate.

In this tutorial we will look at a few examples to show the different ways that the Select Case statement can be written. Continue reading

Select/Deselect all Checkboxes with a Single Checkbox

Add a single checkbox to select, or deselect, all of the checkboxes on a worksheet with one click.

If you have a large list of checkboxes, selecting all of the boxes, or removing the checks from all the boxes can be very time consuming.

This blog post will provide the code to select/deselect all the checkboxes on a worksheet using a single checkbox, and explain how it works.

Watch the Video

Continue reading

Automatically Refresh PivotTable – Excel VBA

Excel PivotTables do not refresh automatically when the data source is updated.

Now, you can refresh all of the PivotTables in your workbook with just 2 or 3 clicks. But this is not something you need to do when you work with formulas, charts or Conditional Formatting. So you might forget, or just find it irritating to do it regularly.

This tutorial walks you through the VBA code to refresh the PivotTable automatically.

New to VBA and want to learn quickly? Sign up to our online Excel VBA course.

Watch the Video

Continue reading

Use Own Icon for Macro Button on Ribbon

In this blog post we look at how you can use your own icon or image for the button of your macro on the Ribbon. This could be an image that you have created, or one that you downloaded from a website.

By using our own icons we can create customised workbooks and not be constrained by the standard Microsoft icon set provided.

In this example I have a macro that exports all the worksheets of a workbook to PDF, and I want to assign it to the icon shown below. This icon was downloaded from flaticon.com, a fantastic resource for icons that you can use for your macros.
Custom icon for macro button Continue reading