• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel VBA

Checking values in range objects with vba

Checking Values in Range Objects With VBA

October 30, 2019 by Philip Treacy

One thing I often found frustrating with VBA is the lack of any easy way to see the values in a range object. I often use Debug.Print when debugging my VBA and I'd love to just be able to Debug.Print a range like you can with other variables. But because a range is an object, …

Read moreChecking Values in Range Objects With VBA
Static variables in VBA

Static Variables in VBA

April 4, 2019 by Philip Treacy

Normally variables cease to exist once your Sub or Function has finished executing. Static variables allow you to preserve a value after your code has finished. Let's say you want to keep track of the number of times something happens, like how many times someone clicks on this button they are not supposed to.

Read moreStatic Variables in VBA
save chart as image

Save Chart as Image

November 30, 2018 by Philip Treacy

You might want to save your charts as images that can then be used in other applications, like email or displayed on a website. Selecting a single image then copying/pasting or saving it, isn't too much bother. But if you want to do that for several images it quickly becomes tiresome. I've written some VBA, …

Read moreSave Chart as Image
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

August 25, 2016 by Philip Treacy

Following on from Mynda's post last week on Dependent Data Validation, I've written a little VBA that enhances the functionality. What this code does is to clear any cells dependent on the value of another data validation list. I'll call these downstream cells. So if I change the Country, I'm going to clear the values …

Read moreClear Downstream Dependent Data Validation Lists
Excel Status Bar

Excel Status Bar

June 15, 2016 by Philip Treacy

In my previous blog post I created a progress bar using a userform. We can use this progress bar to communicate to our Excel user that our VBA code is still running. In this post I’m going to look at using the Status Bar to do the same thing, and create something like this. Status …

Read moreExcel Status Bar
Progress Bar for Excel VBA

Excel Progress Bar for VBA

June 9, 2016 by Philip Treacy

If your code runs for any noticeable amount of time, you might find that using some sort of visual feedback is useful for your users. Letting them know just how long something will take to complete, and just as importantly, letting them know that Excel hasn't hung, reassures them that the code is working and …

Read moreExcel Progress Bar for VBA
error handling in vba

Error Handling in VBA

December 4, 2015 by Philip Treacy

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 …

Read moreError Handling in VBA
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

November 6, 2015 by Philip Treacy

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 …

Read moreFinding File Meta Data Using FileSystemObject
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

September 25, 2015 by Philip Treacy

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', …

Read moreAutomatically Add Items to Data Validation List
Calculate end of period dates in Excel

Excel End of Period Dates

September 11, 2015 by Mynda Treacy

“Funny” days versus funny “Days” Guest post by Roger Govier, Microsoft Excel MVP Do you ever have “Funny” days? You know, the days when you start thinking about a problem, overcomplicate it and get carried down a long and spurious path where your vision becomes totally tunnelled and you can’t “see the wood for the …

Read moreExcel End of Period Dates
add data to combo box drop down list in excel form

Add Data to Combo Box Drop Down List in Excel Form

September 4, 2015 by Philip Treacy

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 …

Read moreAdd Data to Combo Box Drop Down List in Excel Form
Excel forms, insert, update, delete data

Excel Forms – Insert, Update and Delete

August 21, 2015 by Philip Treacy

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 …

Read moreExcel Forms – Insert, Update and Delete
excel date picker

Excel Date Picker

July 9, 2015 by Philip Treacy

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 …

Read moreExcel Date Picker
excel userforms

Excel Forms

July 2, 2015 by Philip Treacy

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 …

Read moreExcel Forms
Highlight Selected Cell In Excel and Preserve Cell Format

Highlight Selected Cells in Excel and Preserve Cell Formatting

May 20, 2015 by Philip Treacy

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 …

Read moreHighlight Selected Cells in Excel and Preserve Cell Formatting
Workbook & Worksheet Events in Excel VBA

Workbook & Worksheet Events in Excel VBA

November 19, 2014 by Philip Treacy

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 …

Read moreWorkbook & Worksheet Events in Excel VBA
Change the color of words in text

Change the Color of Words in Text

September 23, 2014 by Philip Treacy

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 …

Read moreChange the Color of Words in Text
Protect and Unprotect All Sheets in a Workbook

Protect and Unprotect All Sheets in a Workbook

June 18, 2014 by Philip Treacy

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 …

Read moreProtect and Unprotect All Sheets in a Workbook
Customize the Ribbon in Excel

Customize the Ribbon in Excel

June 18, 2014 by Philip Treacy

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 …

Read moreCustomize the Ribbon in Excel
Customize the QAT (Quick Access Toolbar) in Excel

Customize the QAT (Quick Access Toolbar) in Excel

June 17, 2014 by Philip Treacy

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 …

Read moreCustomize the QAT (Quick Access Toolbar) in Excel
Macro Enabled Excel Templates

Macro Enabled Excel Templates

April 9, 2014 by Philip Treacy

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 …

Read moreMacro Enabled Excel Templates
Unhide Multiple Worksheets in Excel

Unhide Multiple Worksheets in Excel

April 1, 2014 by Mynda Treacy

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 …

Read moreUnhide Multiple Worksheets in Excel
Create a Personal Macro Workbook PERSONAL.XLSB

Create a Personal Macro Workbook PERSONAL.XLSB

April 1, 2014 by Philip Treacy

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 …

Read moreCreate a Personal Macro Workbook PERSONAL.XLSB
Create Barcodes With (Or Without) Excel VBA

Create Barcodes With (Or Without) Excel VBA

February 27, 2014 by Philip Treacy

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 …

Read moreCreate Barcodes With (Or Without) Excel VBA
Convert Numbers (Currency) to Words With Excel VBA

Convert Numbers (Currency) to Words With Excel VBA

February 7, 2014 by Philip Treacy

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 …

Read moreConvert Numbers (Currency) to Words With Excel VBA
https://www.myonlinetraininghub.com/assign-a-macro-to-a-shape-or-use-a-shortcut-sequence

Assign a Macro to a Shape or Use a Shortcut Sequence

December 11, 2013 by Philip Treacy

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 …

Read moreAssign a Macro to a Shape or Use a Shortcut Sequence
How To Enable the Developer Tab in Excel

How To Enable the Developer Tab in Excel

November 27, 2013 by Philip Treacy

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 …

Read moreHow To Enable the Developer Tab in Excel
VBA to Create PDF from Excel Worksheet Then Email It With Outlook

VBA to Create PDF from Excel Worksheet Then Email It With Outlook

October 14, 2013 by Philip Treacy

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 …

Read moreVBA to Create PDF from Excel Worksheet Then Email It With Outlook
Merge Excel Worksheets with VBA

Merge Excel Worksheets with VBA

October 1, 2013 by Philip Treacy

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 …

Read moreMerge Excel Worksheets with VBA
Finding Missing Numbers in a Range Using VBA

Finding Missing Numbers in a Range Using VBA

September 11, 2013 by Philip Treacy

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 …

Read moreFinding Missing Numbers in a Range Using VBA
  • Go to page 1
  • Go to page 2
  • Next

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.