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
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
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
Sometimes it is the small things which make the biggest differences. The following tips are small, some of them very small, but they might save you a lot of time and a lot of annoyance. Quick Excel Tip #1 If the formula Tooltip box is in the way, you can move it. Click on Read More
Every office I have ever worked in has been an equipment battleground. It didn’t matter what the equipment was; it just had to be better than everybody else. A new keyboard, a bigger screen, even a mouse mat with a wrist rest was enough to make everyone else jealous. I was once lucky enough to Read More
Listing the files in a folder is one of the activities which cannot be achieved using normal Excel formulas. I could tell you to turn to VBA macros or PowerQuery, but then any non-VBA and non-PowerQuery users would close this post instantly. But wait! Back away from the close button, there is another option. For 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
A step chart is used to show data which changes at specific points, then remains consistent until the next change occurs. Interest rates set by central banks across the world follow this system. Some dudes in suits meet together on a particular day and decide the rate should change. That change happens instantly and remains Read More
The John Lennon song Happy Xmas (War is Over) has been playing almost every hour on UK radio for the past month. At the start of December the lyrics “Another year over, and a new one just begun” seemed slightly premature. But, as I write this it is the end of December the New Year 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