Blog Archives

Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells

Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells.

Tom Urtis gives us another one.

For link formulas in your workbook, you can list their sheet and cell locations, AND the name of their precedent source workbook,

Read more ›

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

Tom’s Tutorials for Excel: Workbook Suicide

Tom’s Tutorials for Excel: Workbook Suicide: Make a workbook delete itself.
Tom Urtis has an intriguingly titled new one today.

If you have developed a workbook which you want to self-expire by a certain date, such as a demonstration model or containing information or usefulness that will be outdated,

Read more ›

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

Tom’s Tutorials for Excel: Running Sum in Same Cell

Tom’s Tutorials for Excel:Running Sum in Same Cell

Here is a WorksheetChange event in VBA that allows any cell in column A to accept a number you enter, add it to whatever number was already in that same cell, and display the resulting sum. For example, if cell A9 currently holds the number 2 and you enter the number 3 in that cell,

Read more ›

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

Tom’s Tutorials for Excel: Fill empty cells with value from above

Tom’s Tutorials for Excel: Fill empty cells with value from above.
Today we have great new tip from Tom Urtis complete with screen shots. Enjoy & Excel!

Step 1
Select the range you want to work with,

Read more ›

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

Tom’s Tutorials for Excel: How to sort your sheet tabs in alphanumeric ascending order

Tom’s Tutorials for Excel:How to sort your sheet tabs in alphanumeric ascending order
Happy Friday! Here is today’s useful Excel solution from Tom Urtis

Sub SortSheetsTabName()
Application.ScreenUpdating = False
Dim iSheets%, i%, j%
iSheets = Sheets.Count
For i = 1 To iSheets – 1
For j = i + 1 To iSheets
If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Remember,

Read more ›

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

Tom’s Tutorials for Excel: 3 Methods to Display Sheet Tab Names in a Cell

Tom’s Tutorials for Excel: 3 methods to display sheet tab name in a cell
“How do you display a sheet tab name in a cell?” is one of the questions Tom Urtis is most frequently asked.
Here you have 3 useful answers

Method 1 – by formula,

Read more ›

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

Tom’s Tutorials for Excel: Closing open workbooks with unknown names

Tom’s Tutorials for Excel: Closing open workbooks with unknown names using Excel.

Step 1
Start by closing all workbooks in Excel except for the workbook that you want this functionality for.

Step 2
In this lone open workbook, from any worksheet press Alt+F11 to go into the Visual Basic Editor.

Read more ›

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

Tom’s Tutorials for Excel: Data Validation from Custom List

Tom’s Tutorials for Excel: Data Validation from Custom List
A special solution that Tom has in his ‘Most Wanted’ file.

Data Validation does not directly access Custom Lists, but with VBA you can establish Data Validation in a cell, with the list of drop-down items being that of one of your Custom Lists.

Read more ›

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

Tom’s Tutorials for Excel: How to Create or Replace a Worksheet

Tom’s Tutorials for Excel: How to verify a worksheet exists, create if not.
The following examples show how to determine if a worksheet exists, and then how to create or replace the worksheet

Determining if a Worksheet Exists

This example shows how to determine if a worksheet named “Sheet4” exists by using the Name property of the Worksheet object.

Read more ›

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

Tom’s Tutorials for Excel: Automatically Dismiss a Message Box

Tom’s Tutorials for Excel: Automatically Dismiss a Message Box

This example shows how to automatically dismiss a message box after a specified period of time. The message box is shown, then automatically dismisses itself after 10 seconds.

Here’s the example macro:

Sub MessageBoxTimer()
Dim AckTime As Integer,

Read more ›

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