• 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

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 form data validation

Excel Form Data Validation

July 9, 2015 by Philip Treacy

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 …

Read moreExcel Form Data Validation
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
Snail

Pausing or Delaying VBA Using Wait, Sleep or A Loop

March 30, 2015 by Philip Treacy

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 …

Read morePausing or Delaying VBA Using Wait, Sleep or A Loop
find missing numbers really fast

Find Missing Numbers, Really Fast

February 19, 2015 by Philip Treacy

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 …

Read moreFind Missing Numbers, Really Fast
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
Create Hyperlinked List of Files in Subfolders

Create Hyperlinked List of Files in Subfolders

May 20, 2014 by Philip Treacy

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 …

Read moreCreate Hyperlinked List of Files in Subfolders
Create Hyperlinked List of Files in a Folder Using VBA

Create Hyperlinked List of Files in a Folder Using VBA

April 16, 2014 by Philip Treacy

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 …

Read moreCreate Hyperlinked List of Files in a Folder Using VBA
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
Trusted Locations

Trusted Locations

April 9, 2014 by Philip Treacy

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 …

Read moreTrusted Locations
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 Use VBA Code From The Internet

How To Use VBA Code From The Internet

November 27, 2013 by Philip Treacy

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 …

Read moreHow To Use VBA Code From The Internet
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
Excel Factor 8 Highlight Cells Containing Formulas

Excel Factor 8 Highlight Cells Containing Formulas

August 3, 2012 by Mynda Treacy

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

Read moreExcel Factor 8 Highlight Cells Containing Formulas
list sheet tab names with vba

List Sheet Tab Names with Excel VBA

May 10, 2012 by Mynda Treacy

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 …

Read moreList Sheet Tab Names with Excel VBA
  • Previous
  • Go to page 1
  • Go to page 2
  • Go to page 3

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.