Blog Archives

Tom’s Tutorials For Excel: Showing And AutoClosing a UserForm

Tom’s Tutorials For Excel: Showing And AutoClosing a UserForm

Sometimes you might want to only show a UserForm for a brief time, say 5 seconds. For example, you might want to show a quick message that’s custom-formatted beyond what a MessageBox can do, or you want the effect of a splash screen to advertise your work when the workbook opens.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Force an Entry Into an InputBox

Force an Entry Into an InputBox

Previously, I posted this example for capturing Cancel with InputBoxes.

In some development situations, you may need to force the user to make an entry to an InputBox.

The below example is one way to accomplish this,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Updating Links (or Not)

Tom’s Tutorials For Excel: Updating Links (or Not)

You’ve no doubt seen this pop-up message upon opening a workbook:

To automatically update links sans VBA, if the work being done is always on your computer…
…In versions 2003 or before: Tools >

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Refreshing All Your PivotTables at Once

Tom’s Tutorials For Excel: Refreshing All Your PivotTables at Once

Here are two macros you can use for refreshing all the pivot tables on your active worksheet, or all the pivot tables in the entire worbook.

Sub RefreshAllActiveSheet()
Dim Pt As PivotTable
For Each Pt In ActiveSheet.PivotTables
Pt.RefreshTable
Next Pt
End Sub

Sub RefreshAllActiveWB()
Dim Sh As Worksheet,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table

Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table

If you’d appreciate the convenience of having your pivot table automatically refresh whenever you change its source data, here’s how you can accomplish that.

In the Before picture, the source data’s State field lists American states.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Suppose your worksheet requires frequent entries in a column, and you want to automatically record the date and time of whenever a cell in that column changes.

In the picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting All Formula Cell References From Relative to Absolute

Tom’s Tutorials For Excel: Converting All Formula Cell References From Relative to Absolute

When you have a lot of formulas on a worksheet for which you want to convert all cell and range references from relative to absolute, this macro can do the job:

Sub ConvertRelativeToAbsolute()
Dim cell As Range,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Maximizing Your UserForm to Full Screen Size

Tom’s Tutorials For Excel: Maximizing Your UserForm to Full Screen Size

To fill the screen with your UserForm while maintaining the relative position of the form’s controls, stick this Initialize event code into the UserForm’s module:

Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
End Sub

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Preventing Save or Close Unless Cells are Filled

Tom’s Tutorials For Excel: Preventing Save or Close Unless Cells are Filled

Sometimes you may want to prevent the workbook from being saved or closed unless data has been entered into certain cells, such as for on-sheet forms. The VBA code to accomplish this would go into your workbook module.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window

Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window

You can automatically position your UserForm almost anywhere on your Excel application’s window, with the following Initialize event code that goes into the UserForm’s module.

Example 1, upper left

Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Dim Top As Double,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell

Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell

Here’s an easy way to keep a running log of changes to a cell’s text.

Suppose you want your employees to enter an explanation or description of some kind into a cell regarding a topic on your spreadsheet.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Set Page Breaks For Specified Areas

Tom’s Tutorials For Excel: Set Page Breaks For Specified Areas

If your worksheet has areas of data that you want to print on separate pages, you can easily establish page breaks based on a wide choice of cell properties or text values. With the following macro for the pictured example,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: VBA Help — Usually Right, Sometimes Wrong

Tom’s Tutorials For Excel: VBA Help — Usually Right, Sometimes Wrong

The above picture shows a simple macro that calls a Message Box to display a tab name by referring to the active sheet’s CodeName property. Suppose you’ve never before heard the term CodeName,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Blinking Cells and Flashing Objects

Tom’s Tutorials For Excel: Blinking Cells and Flashing Objects

Free and safe downloadable workbook with blink examples

With VBA, you can make cells or their contents look as if they are blinking or flashing. This is not a popular tactic because, although amusing at first,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Automatically Show UPPER Case Text In Cells

Automatically Show UPPER Case Text In Cells

If you want to make sure that text entries in lower case — even alphanumeric entries — will automatically be displayed in UPPER case as soon as the entry is made, here’s how to do it.

Let’s say you want range A1:A10 to always show upper case letters.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Auto-Sort By Double-Clicking a Cell

Tom’s Tutorials for Excel: Auto-Sort By Double-Clicking a Cell

This example toggles a sort in ascending and descending order for a table of data whose headers are in row 1. When you double-click any cell in the data range, the table is sorted based on the column of the cell that is double-clicked.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Open a Workbook To a Specific Sheet and Cell

Tom’s Tutorials For Excel: Open a Workbook To a Specific Sheet and Cell

How can you open a workbook to a specific worksheet and cell? For example, regardless of where you were in the workbook when it was last closed, if you always want the workbook to open by taking you to cell A1 of Sheet3,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: How to Show Your Workbook’s Name in a Cell

Tom’s Tutorials For Excel: How to Show Your Workbook’s Name in a Cell

Here are a few ways to show your workbook’s name in a cell, example, YourWorkbookName.xls.

Formula (goes on one line but broken into 2 lines for browser readability)
=MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,FIND(“]”,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Name your sheet tab same as cell value.

Name your sheet tab same as cell value.

You can rename a worksheet in real time the moment you change a cell’s value.

A Worksheet_Change event can look at the entry, evaluate it for worthiness, and immediately rename the worksheet tab to be the same as what you entered in the cell.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Does a Worksheet Exist, and Create One if Not?

Tom’s Tutorials For Excel: Does a Worksheet Exist, and Create One if Not?

There are three scenarios usually associated with this question.

Scenario #1:
Simply yes or no, does a certain worksheet name exist in the active workbook?

Sub TestSheetYesNo()
Dim mySheetName As String,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only

Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only

Here’s an example of controlling what a user enters into a TextBox on your userform, in this case, allowing only five whole numbers to be entered.

Suppose you are collecting mailing addresses for your customers,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Delete a Table in Access From Excel

Tom’s Tutorials For Excel: Delete a Table in Access From Excel

Sometimes you use Excel to manipulate other Office applications such as Word, PowerPoint, and Access. This macro shows how to connect to an Access database and delete a particular table.

A reference to the Microsoft ActiveX Data Objects 2.8 Library is required for the code to run.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Count Your Workbook’s Total Printable Pages

Tom’s Tutorials For Excel: Count Your Workbook’s Total Printable Pages

Ever want to know how many total pages would print from all your workbook’s worksheets?

This macro will tell you:

Sub GetPrintablePageCount()
Dim iSheet%, iCountStart%, iCountEnd%
iCountStart = 0: iCountEnd = 0
For iSheet = 1 To Worksheets.Count
If Sheets(iSheet).Visible = xlSheetVisible Then
Sheets(iSheet).Activate
iCountStart = iCountEnd + 1
iCountEnd = iCountEnd + ExecuteExcel4Macro(“INDEX(GET.DOCUMENT(50),1)”)
MsgBox “The sheet named ” & Sheets(iSheet).Name & vbCrLf & _
“has its printable pages starting at number ” & iCountStart & vbCrLf & _
“and ending at number ” & iCountEnd,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: *Really* Clear the Clipboard

Tom’s Tutorials For Excel: *Really* Clear the Clipboard

The Windows Clipboard is a temporary storage area for information that you have copied or moved from one place and plan to use somewhere else. You cannot see or touch the Clipboard but you can work with it to copy,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding and Deleting Phantom Links

Tom’s Tutorials For Excel: Finding and Deleting Phantom Links

If you’ve ever wondered why this message pops up after you are sure you’ve deleted all your workbook’s links, well, join the club. It’s a common problem because some links can be difficult to find.

In my 20 years working with Excel,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Fill Formula to Last Used Row

Tom’s Tutorials For Excel: Fill Formula to Last Used Row

The picture on the left shows a list of numbers in column A starting in cell A2. You have a formula in cell B2 which you want to quickly copy down to the last used cell in column A.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Message Box “Carriage Return”

Tom’s Tutorials For Excel: Message Box “Carriage Return”

To start a new line in your Message Box prompt, insert a Visual Basic Carriage Return Line Feed character, coded as vbCrLf.

Example macros:

Sub MessageBoxNewLinesA()
MsgBox “This is sentence 1.” & vbCrLf & “This is sentence 2.” & vbCrLf & “This is sentence 3.”
End Sub

You can use the underscore character for easier readability of a lengthy code line:

Sub MessageBoxNewLinesB()
MsgBox _
“This is sentence 1.” & vbCrLf & _
“This is sentence 2.” & vbCrLf & _
“This is sentence 3.”
End Sub

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Workbook Creation Date

Tom’s Tutorials For Excel: Workbook Creation Date

If you want to know the creation date and time of the active workbook:

Sub MyCreationDate()
Dim myDate As Date
myDate = ActiveWorkbook.BuiltinDocumentProperties(“Creation date”).Value
MsgBox Format(myDate, “mmmm d, yyyy at h:mm:ss AM/PM”), , _
“I was born on…”
End Sub

If you want to know the creation date and time of a closed or other workbook:

Sub CreationDateWB()
Dim objWB As Object,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Open, Modify, and Close All Workbooks in a Folder

Tom’s Tutorials For Excel: Open, Modify, and Close All Workbooks in a Folder

This topic touches on a surprise we all got when Microsoft released version 2007, which stopped supporting the VBA FileSearch method. When you need to open all the workbooks in a folder, modify them in some way,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Save Each Worksheet As Its Own Workbook

Tom’s Tutorials For Excel: Save Each Worksheet As Its Own Workbook

For the active workbook, this macro saves each visible worksheet as its own workbook in the current path. Workbooks are named as their sheet tab name, example, Sheet1’s workbook name would be “Sheet1.xls”.

Sub SaveWorksheetsAsWorkbooks()
Application.ScreenUpdating = False
Dim ws As Worksheet,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Capturing Cancel With InputBoxes

Tom’s Tutorials For Excel: Capturing Cancel With InputBoxes

In VBA, there are two kinds of InputBoxes: The InputBox Function and the InputBox Method. Visually, they appear similar and in fact serve most of the same purposes, with the InputBox Method providing optional argument parameters for specifying inputs for text,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Move Embedded Charts to Their Own Chart Sheet

Tom’s Tutorials For Excel: Move Embedded Charts to Their Own Chart Sheet

Here is a macro that will move all embedded charts on a worksheet to their own Chart sheet.

The Select method for cell A1 (it could be any cell but I chose A1) is to avoid a run time error which could occur if an embedded chart is active when a new sheet is attempted to be added.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Export Word To Excel

Tom’s Tutorials For Excel: Export Word To Excel

For importing part of a Word document into your Excel workbook, here is a macro that imports the second paragraph from a Word doc.

Sub ExportWordToExcel()
‘Declare object variables.
Dim WdApp As Object, wddoc As Object
‘Open Word
Set WdApp = CreateObject(“Word.Application”)
‘Open the Word document
Set wddoc = WdApp.Documents.Open(Filename:=”C:\Your\File\Path\myWordDoc.docx”)
‘Copy paragraph 2
wddoc.Paragraphs(2).Range.Copy
‘Activate your workbook and go to the paste destination of Sheet1 cell A1.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Sometimes you have a list for which you need to delete rows where a cell is blank in a particular column. If the list is small, you can eyeball each blank cell, select it, and delete the row manually,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Hide the right-click Mini Toolbar

Tom’s Tutorials For Excel: Hide the right-click Mini Toolbar

In versions 2007 and 2010, there is a mini toolbar that appears when you right-click a worksheet cell. There is an option to show that mini toolbar but not an option to hide it.

In Picture #1,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Taking Stock

Tom’s Tutorials For Excel: Taking Stock

Here’s a fast and easy way to download the daily pricing activity of a company’s stock, from the first day it went public up to its last closing date. The following macro uses Microsoft as an example.

Microsoft’s stock abbreviation is MSFT as defined by the strSymbol variable.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: ListBox Days & ComboBox Months

Tom’s Tutorials For Excel: ListBox Days & ComboBox Months

In your userform’s module, this Initialize event code populates a ListBox with days of the week, and a ComboBox with months of the year, as seen by example in the picture.

Private Sub UserForm_Initialize()

‘Populate a ListBox with days of the week.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Delete all Pivot Tables

Tom’s Tutorials For Excel: Delete all Pivot Tables

You may find the need to delete all pivot tables that are on a worksheet, or that are in the entire workbook.

This first macro deletes all pivot tables on the active worksheet:

Sub DeletePivotTablesWorksheet()
Dim objPT As PivotTable,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Selecting a Dynamic Range

Tom’s Tutorials For Excel: Selecting a Dynamic Range

Here is a contribution by Tom Urtis to the library of MSDN.

Sometimes you need to locate and manipulate a range of data on the worksheet, but you will not know where the data starts, where it ends,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Subtotaling Filtered Data

Tom’s Tutorials For Excel: Subtotaling Filtered Data

With the SUBTOTAL function you can perform mathematical operations on visible filtered data.
The SUBTOTAL function wants two pieces of information from you:
(1) The type of operation (SUM, AVERAGE, and so on) you want to perform.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text

Tom’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text

At some point you have probably inherited or created a worksheet that contains active hyperlinks, when all you want in the cells is the hyperlink text itself. It’s annoying to unwittingly click onto a hyperlink cell that interrupts your work by suddenly taking you away from your worksheet.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Attach Workbook in Outlook Email to Multiple Recipients

Tom’s Tutorials for Excel: Attach Workbook in Outlook Email to Multiple Recipients

With a macro from Excel, you can create an email in Outlook that will attach your active workbook and fill in the To field with a list of multiple recipients. Suppose you have a workbook that you email to the same people on a regular basis.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Unique List From Multi-Column Table

Tom’s Tutorials For Excel: Unique List From Multi-Column Table
You may know that from a single-column list containing repeated items, you can extract a list of unique items using Advanced Filter. But what if you want to extract a unique list from a table having many columns of repeatedly listed items?

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Count Your Workbook’s Formulas

Tom’s Tutorials for Excel: Count Your Workbook’s Formulas

Here is a macro that goes sheet by sheet through your workbook, and shows you a Message Box with the count how many formulas (if any) are on each sheet. Substitute your sheet’s actual password with the sample “YourPassword”

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Export Excel Spreadsheet Into Access Database Table

Tom’s Tutorials For Excel: Export Excel Spreadsheet Into Access Database Table

Here’s the latest of the Tom’s Tutorials for Excel series in which Tom Urtis walks you through a solution step-by-step. If you have any questions please ask them here in comments. Tom takes the time to be sure readers of every level of expertise feel confident using Excel.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Sum and Count Cells By Color

Tom’s Tutorials for Excel: Sum and Count Cells By Color

To sum, count, or perform a mathematical operation on a range of colored cells, you will need VBA. Excel does not have a built-in native worksheet function to mathematically evaluate contents of colored cells, such as summing the numbers in all red-colored cells.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Sheet Selector Drop-Down List

Tom’s Tutorials for Excel: Sheet Selector Drop-Down List.

Download example workbook: ComboBox_SheetSelector

Although you can right-click the sheet navigation buttons at the left of the sheet tabs to display a list of sheets, where you can click a sheet name to select it,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Print a Word document from Excel

Tom’s Tips for Excel: Print a Word document from Excel

Here is the Tuesday blog length post from Tom Urtis. This one is so handy!

Sub PrintWordDoc()
‘Declare object variables.
Dim WdApp As Object, wddoc As Object
‘Open Word
Set WdApp = CreateObject(“Word.Application”)
‘Open the Word document to be printed.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Color the active cell, row, or column

Tom’s Tutorials for Excel: Color the active cell, row, or column.
Here’s Tom Urtis’ Monday long tip. Any questions? Just leave us a comment.

Sometimes you want to see where you are on the worksheet, or highlight relevant ranges as you navigate,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: How to verify if a workbook exists

Tom’s Tutorials for Excel: How to verify if a workbook exists.
Tom Urtis’ latest workbook tip.

When verifying if a certain workbook name exists, you would also want to verify if the source path and folder to which it supposedly belongs exists.

It is possible for the workbook to exist outside the source folder where the user thinks it should be,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,