Excel VBA Archives • Page 3 of 6 • My Online Training Hub

Highlight Text, Numbers, Dates and Formulas on a Sheet

April 6, 2016 by Philip Treacy 12 Comments

Imagine that you are preparing data for a pivot table, and you want to make sure that all the cells contain the correct data type. We know that a date or number entered as text can mess things up so if we had a quick way to identify these problems, it could save a lot […]

Get Started With VBA – 3 Easy Examples

March 15, 2016 by Philip Treacy 5 Comments

You can create very complicated and powerful VBA routines in Excel, but not all VBA code has to be like this. VBA is here to make your life easier. Even simple, little things can speed up what you do. Whether you use the macro recorder, or write your own code, they are not hard to […]

Selecting Multiple Items in Data Validation List

March 3, 2016 by Philip Treacy 4 Comments

If you want to limit the choices available for the values that can be entered into a cell, you could use a data validation list. In the example image below, we are creating a roster for the teachers that are on playground duty during the week. The data is laid out as a table, and […]

Error Handling in VBA

December 4, 2015 by Philip Treacy 4 Comments

If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you’ll see a default error message like this: But you can change this and instruct Excel to allow your code to deal with run-time errors. NOTE : I’m going to use […]

Finding File Meta Data Using FileSystemObject

November 6, 2015 by Philip Treacy 4 Comments

In a previous post I looked at using the FileSystemObject to search through folders on your computer’s disk and create a hyperlinked list of files. Clicking on the hyperlink would open the file. FileSystemObject (FSO) can also be used to retrieve meta data from a file, like the creation date, last modified date or file […]

Save Settings in Registry Using Excel

October 1, 2015 by Philip Treacy 2 Comments

If you want to save data from one Excel session to another, you can store this in the Windows registry. Using the registry is good for saving things like user preferences or program configuration data. There are other means of saving this type of data like external files, hidden sheets and defined names, but I’m […]

Automatically Add Items to Data Validation List

September 25, 2015 by Philip Treacy 14 Comments

If you are using data validation (and you should) to help you with your data entry, then you might find that the list you are using doesn’t have all the values you want. For example, if you were using a list to assign a priority to some work, your list may consist of ‘Not Started’, […]

Add Data to Combo Box Drop Down List in Excel Form

September 4, 2015 by Philip Treacy 6 Comments

If you’ve been following the previous posts in this series you’ll know that by now we have a user form that allows us to enter data into a table, and allows us to update and delete existing records. If you haven’t read the other posts about Excel Forms, here are the links. Excel Forms Create […]

Excel Forms – Insert, Update and Delete

August 21, 2015 by Philip Treacy 44 Comments

Carrying on with the Excel form I created in an earlier post, I’m adding code to allow us to insert new records into any row of the table (not just at the bottom of the table), delete records and update existing records. Related Posts: This is the last part of a series of posts I’ve […]

Excel Form Data Validation

July 9, 2015 by Philip Treacy 14 Comments

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 […]