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.
…
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,
…
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 >
…
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,…
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.
…
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,
…
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,…
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…
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.
…
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,
…
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,
…
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,
…
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,
…
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.
…
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.
…
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,
…
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(“]”,
…
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.
…
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,…
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,
…
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.
…
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,…
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,
…
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,
…
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.
…
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…
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,…
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,
…
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,…
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,
…
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.
…
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.
…
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,
…
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,
…
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.
…
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.
…
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,
…
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,
…
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.
…
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.
…
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.
…
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?
…
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”
…
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.
…
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.
…
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,
…
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.…
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,
…
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,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
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 ›
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Comments / Screen Tips, Data Science, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis