I use the terms Sheet and Worksheet interchangeably when talking about Excel, I think most users do. Google also appears to think they are the same thing; If I search for “How to loop through Sheets with Excel VBA”, all the results returned on the first page refer to Worksheets. Yet, Sheets and Worksheets from Read More
Category: VBA
Convert Merged cells to Centre Across Selection with VBA
To merge or not to merge? That is the question. And it’s a questions which divides the Excel community. We find the Merge & Center button is on the Ribbon, so it appears to be the only option. But soon we are greeted by these types of annoying error messages: Excel has a secret alternative Read More
Private vs Public Subs, Variables & Functions in VBA
we When writing VBA macros, the concept of Private or Public is important. It defines how VBA code within one module can interact with VBA code in another module. On social media you can to set parts of your profile so that everybody can see it (Public), or only those you allow, such as friends Read More
How to select all unlocked cells in Excel
Spreadsheets are easy to break. All it takes is one press of a key and suddenly every formula is displaying in an error message. That’s not good! This is why we apply worksheet protection, to prevent user error. Some think that worksheet protection is secure, however password protected worksheets can easily be unlocked with a Read More
Name an Excel Table when created
I love the Tables feature of Excel, it just makes everything else in the spreadsheet easier to work with. However, one of the biggest frustrations is that there is no option to name the Table at the time of creation. By default, we are given a unique name like Table1 if it is the first Read More
Sorting ListBoxes with VBA
I received a question from a reader recently, who wanted to sort a Listbox using VBA. As this seemed like a common problem which many users could face, I decided to dedicate a bit of time to create a more reusable solution. In this post, we will look at two different types of Listboxes: UserForm Read More
VBA code to contol Form Control checkboxes
Checkboxes are one of the easiest ways to create interaction with users. Controlling them with VBA makes them more flexible and more useful in Excel applications. Here is a collection of the most common code snippets and settings. Create Check Boxes The following code will create a checkbox Sub CreateCheckBoxes() ‘Create variable Dim chkBox As Read More
Assign macro with arguments to a Form Control button
One of the most popular methods of running a macro is having a button on the face of the worksheet. These are simple enough to create with the basic Form Controls found on the Developer Ribbon, which is what makes them a popular option. But what if you had lots of buttons, all of which Read More
Create multiple checkboxes instantly with VBA
Checkboxes placed onto the face of the worksheet are an excellent way for users to interact with a spreadsheet. Each checkbox can be linked to a cell, so that when clicked the value in the cell changes between TRUE and FALSE. But if you have a lot of checkboxes to create it becomes a real Read More
VBA code to control and manage Excel Tables
Tables are one of the most powerful features of Excel. Being able to automate them using VBA provides a way to make Tables even more powerful. Creating and converting Tables This first set of macros is about creating and resizing macros. Convert selection to Excel Table This macro will create a Table based on the Read More