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
Category: VBA Code Snippet
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
Run a macro from a macro (from another workbook)
In VBA, there are several ways to run a macro within a macro, and even run a macro from another workbook. On forums these methods are used interchangeably depending on the commenter’s personal preferences. This can lead to confusion for anybody trying to learn VBA. Depending on the method for running/calling the macro, the syntax Read More
VBA Code to control Excel’s Zoom settings
Excel has zoom settings for worksheets, UserForms and print settings. Each of these serves different purposes and used at different points, but they can all be controlled by VBA. Worksheet zoom settings The worksheet zoom setting in Excel will always be an integer. If the value set by VBA contains decimal places it will be Read More