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
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
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
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
Titles are an overlooked aspect of many charts. A bland title misses out on so much rich information which could be used to enhance a users understanding of the report. Here is an example of bad and good chart titles: The 2nd chart title adds additional insight and provides a much richer experience for the … 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 applied … Read more
“Not again…” I think to myself every time I change the minimum and maximum values of a chart axis. It’s such a tedious task, and I know I’ll be doing it again at a future point. It only takes a few seconds, but all that time starts to add up. There are various chart objects we can link … Read more
Wouldn’t it be great to change the color of a worksheet tab based on a calculation? If you’re not convinced, here are some possible scenarios: When a schedule reconciles or includes a specific cell value If there is an error in a range of cells To color code tabs to match a contents page You … Read more
When building Excel-based tools and applications it is often useful for the user to select a color. Whilst it is possible to build a custom UserForm, Excel’s native Color window is more than suitable for most circumstances. Excel’s native color picker: To show the Color window, you might think we could use the Macro Recorder, but … 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 be … Read more