I had an interesting question from a reader recently. He wanted to save a PDF for each item in a data validation drop-down list. In the past, when I have tackled similar tasks, I have always used a named range with all the possible values, then looped through the cells in the named range. My Read More
Category: VBA Code Snippet
VBA code for charts and graphs in Excel
Charts and graphs in Excel have hundreds of different options. This is great for creating precisely the visualization we want but can be time-consuming to apply. When we want to apply those hundreds of settings to lots of charts, it can take hours and hours of frustrating clicking. The short code snippets below will help Read More
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
VBA code to select all merged cells
Merged cells can be frustrating when using VBA code, as they just don’t operate in the same way as normal cells. Even when using Excel without VBA, merged cells cause a whole host of frustrating error messages. Even if you don’t use them, lots of other users do. So you will encounter merged cells in Read More
VBA code draw rectangles around selected cells
When creating reports, it is sometimes necessary to highlight cells to draw attention to them. The most obvious method to achieve this is to change a Cell’s border color, I did that for many years. It’s quick to apply, but the pain comes when it’s time to remove the color, the formatting of every cell 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
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
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
VBA Code to Apply and Control AutoFilter in Excel
AutoFilters are a great feature in Excel. Often they are a quicker way of sorting and filtering data than looping through each cell in a range. This post provides the main lines of code to apply and control the AutoFilter settings with VBA. Adapting the code to your needs Every code snippet below is applied Read More