Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
When creating Excel userforms it seems like a good idea to check the data going into the form before adding it to your sheet. Once the data is in your sheet you can use other methods like conditional formatting to highlight errors or unwanted data. But it's good to stop the errors before they get …
In my previous post about Excel forms the date picker I used was a 32 bit ActiveX control. If mentioning 32 bit ActiveX controls makes your eyes roll back in your head, then this post is for you 🙂 This date picker is implemented as a VBA class. Which means all you need to do …
Excel forms, or Userforms, can be used to collect information from a user, validate it, then enter that data into your worksheet. In this post I am going to show you how to create a form in Excel that gathers data, then populates a table for tracking expenses. This is meant as a simple example …
When you select a cell, or cells, in Excel, the row and column headers change color to indicate what you have selected. As you can see here we have selected B2. Or is it 2B? Hmm, 2B or not 2B? If you have a busy sheet though, you may want a more obvious indication of …
You may want some way of pausing or delaying VBA code execution and you can do this with two functions called Wait and Sleep. You can also do this using a loop, and we will look at that approach too, but first we’ll look at the functions available to Excel. Why would you pause the …
A while ago I wrote a routine using the .Find method to find missing numbers in a sequence. So if we had 1, 3, 5, the missing numbers are 2 and 4. I had some feedback that as the sequence grew longer, the .Find method became increasingly slower, to the point that your hair grew …
What Are Events? An Event occurs when certain actions are performed by the user or VBA. For example when you open a workbook the Workbook_Open event is triggered. When you (or your VBA) saves a workbook, the Workbook_BeforeSave event is triggered. These events are built into Excel VBA and are executed automatically by Excel. You …
One of our students (hi Leah 🙂 ) sent me a question last week asking how she could change the color of words (sub-strings) in text. She was trying to visually indicate where words were on the sheet. She supplied some code that she got somewhere on the net. I'm not sure where from so …
I recently had a client who has a requirement to protect and unprotect a lot of sheets. This was something they didn’t do very often, but when they did, they described the process of doing it manually as cumbersome. I’m sure that once you get more than a few sheets, protecting and unprotecting each one …
You can customize the Ribbon in Excel so that you add your own tabs and icons to run commonly used tasks and tools, or run your macros. NOTE : This applies to Excel 2010 and 2013 only. Modifying the Ribbon in Excel 2007 is a different process which I will cover in another post. Just …
You can customize the Quick Access Toolbar (QAT) in Excel so that you add icons to run your macros, or commonly used tasks and tools. Just follow the 8 steps below, it shouldn't take you more than 5 minutes, if that. You can also modify the Ribbon to show custom tabs, groups and icons for …
I wrote a macro a few weeks ago which created a list of files with hyperlinks to those files, but didn't traverse subfolders looking for those files. So it was limited to the folder you selected. This improved macro will : Ask you what your root (starting) folder is Ask you for the file type …
So you have a load of PDF's, or scanned documents, or some nice pictures of your stamp collection*, and you want to get this list into Excel. But you also want to be able to click on that file name/link and have the document/image open up in Excel. Easy. * I don't have a stamp …
What You Can Do With Macro Enabled Templates This is up to you. Using a template allows you to design a workbook layout to look how you want. Using macros gives you the power to customize that template. Let's say you are creating invoices and each invoice has a new, unique number. VBA can keep …
If you want Excel to stop asking you if you want to enable macros every time you open a macro enabled workbook, you should think about using a trusted location. What’s That? A trusted location is a folder either on your computer or the network that you have told Excel you trust. Basically you are …
I’m sure most of us have experienced frustration at Excel’s refusal to allow us to unhide multiple worksheets. This is no April Fools' joke! I know it seems illogical to allow the hiding of multiple sheets but not the un-hiding. Ho, hum....here are a couple of easy fixes: 1. Custom Views A custom view is …
When you create a macro in Excel, by default, the macro works only in the workbook that contains it. But if you need to use a macro in multiple workbooks what do you do? You can use your own personal macro workbook, called PERSONAL.XLSB. This is a hidden workbook into which you can store macros …
Barcode Types & Uses There are LOTS of barcode types used for a multitude of things. Various internationally recognised standards exist (and some not standardised) for things like retail stock tracking, mail and parcel tracking, ISBN numbers, warehousing, pharmaceutical packaging and many other uses. Like creating secret spy messages to leave pinned up around the …
We get requests now and again asking how to convert numbers to words (or convert currency) e.g. 123.45 becomes One Hundred Twenty Three Dollars and Forty Five Cents. Excel doesn’t provide a function to do this so I’m going to write my own. Microsoft does provide some VBA code which I will use as my …
So you’ve got some code and you want to be able to run it, but opening up the list of macros (ALT+F8) and choosing the one you want just isn’t quick enough. Or if you have a lot of macros it can get tiresome looking through your list to find the right macro before running …
So you’ve found some VBA code on the ‘net that will save you hours and hours of work, but how do you get it into Excel and use it? Often I’ve seen snippets of code posted on sites that won’t work unless you know where to put them in Excel, and how to actually get …
The Developer tab in Excel gives you access to features and tools that help you to write and run macros. It’s not enabled by default so this is what you do to get access to it. Excel 2007 Click the Microsoft Office button (the round button at top left) Click on Excel Options Click Popular …
Real World Scenario One of my customers (thanks Tony) creates invoices in Excel for his clients and at the end of each month he wants to email these invoices to each of those clients. Each invoice is on a separate worksheet. If he has a lot of sheets doing this one by one will quickly …
The Scenario Let’s say that you have a lot of sheets in your workbook and you want to merge all the data onto a single worksheet. If you have your data laid out in the same way on each sheet then this piece of VBA will do the trick for you. Maybe you have sales …
The Problem You have a sequence of numbers, and you want to find the numbers that are missing from that sequence. Let's use some VBA. Watch This Video - It Explains All This video explains how the code works, how to use it, and how to get the code and put it into your own …
This Excel Factor tip was sent in by Shannon Hommel of the UK. If you’re setting up a template or a workbook for other’s to use and you can’t password protect it (for whatever reason), then the next best thing is to highlight the cells containing formulas. This is not only an Excel ‘Best Practice’, …
As I promised last week, today’s tutorial is a dead easy macro that will allow you to extract a list of your worksheet tab names. You can then use this list to: Create an index of your worksheets. Why not add Hyperlinks so you can quickly navigate your workbook. Or use them to create references …
