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
Category: VBA
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
VBA code to insert, delete and control comments
Some Excel users love comments; they put comments all over their workbooks. Other users despise them as they are unsightly and annoying. Whatever your preference, you can control them with VBA; add, delete, list on a worksheet – it can all be done with VBA. Adapting the code to your needs It is unlikely that Read More
3 ways to change or insert images based cell values
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell value. Or you may think if this a formula which changes a picture, rather than a number. Ultimately, the purpose is to link an image or picture to a Read More
Removing / cracking Excel passwords with VBA
There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, or maybe you set the password and have now forgotten it. Either way… you’re stuffed! However, before you give in, let me share some of the methods I use to remove passwords. When messing around with Read More
VBA Code to insert, move, delete and control pictures
It is not often that we have to control pictures or images within Excel. This post serves as a reference to cover most of the scenarios we’re likely to encounter when we do need to. This code can be used in conjunction with the user defined function to insert and change pictures based on a Read More