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 Read More

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 Read More

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

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 Read More

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, which can lead to confusion for others, as depending on the method for running/calling the macro, the syntax is slightly different. This post shows these common methods Read More

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 Read More

Often we want to determine what type of object has been selected in Excel. It helps with error checking prior to running a piece of code.   Basic code This action is just a just a single line of code: Sub testSelection() MsgBox TypeName(Selection) End Sub   Incorporate the check into a Select statement If Read More

It is frustrating that Excel, PowerPoint and Word work natively using a measurement known as points.  However, measurements within the standard application menus are set in centimeters or inches.  This can be seen when setting the position and size of a shape in PowerPoint through the standard menus. Yet, when using a macro to retrieve Read More