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 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 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
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
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
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
If you speak to anybody who works in the accountancy field, they will always be talking about the “month end”. In all medium and large companies there will be a process at each month end which requires statements to be issued and reports to be distributed. This monthly process fills most finance people with a Read More