Blog Archives

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Download this Excel workbook that runs a PowerPoint presentation.
Download this accompanying PowerPoint presentation.

Here is how you can run a PowerPoint presentation from Excel, with the below macro named “PowerPointSlideshow”.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Knowing Any Workbook’s Last Saved Date and Time

You can get the last saved date and time for any workbook whether it is open or closed. In Step 3, for demonstration purposes in the Immediate window, I preceded that line of code with the then-current date and time. In the following pictures, Book1 is the only workbook open in the Visual Basic Editor’s Project window.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Hiding Your Named Ranges

Tom’s Tutorials For Excel: Hiding Your Named Ranges
In the below picture, your workbook’s named ranges can be shown by clicking the down arrow next to the Name box.
TTFE0017a
The following macro hides the named ranges, and as the next picture shows, you can work with hidden named ranges the same as you would when they are visible.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Entering a Static Random Number

Tom’s Tutorials For Excel: Entering a Static Random Number

I previously posted these examples of data entry using random numbers.

In each of those examples, by design, the random numbers returned by the RAND function were volatile. That is, any change to worksheet data would recalculate those formulas,

Read more ›

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

Tom’s Tutorials For Excel: Inserting Numbered Rows of Varying Increments

Tom’s Tutorials For Excel: Inserting Numbered Rows of Varying Increments

As any Excel VBA guru of the workplace knows, it’s you who gets called upon to make sense of whacky downloaded data.

Here, your company’s data warehouse shoots out a file with numbered records in column A that vary because some records are empty.

Read more ›

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

Tom’s Tutorials For Excel: Careful! That was *Alt*+F11 !

Tom’s Tutorials For Excel: Careful! That was *Alt*+F11 !

From your active workbook, a fast and easy way to get into the Visual Basic Editor is to press Alt+F11 on your keyboard. You can do this from any worksheet.

Careful, that was *Alt*+F11! The Ctrl key is commonly used in conjunction with other keys for keyboard shortcuts.

Read more ›

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

Tom’s Tutorials For Excel: Random Ramblings

Tom’s Tutorials For Excel: Random Ramblings

Here’s something to think about regarding random numbers in Excel.

Random numbers are based on a predictable algorithm. If you know the previous number and the algorithm, you can predict the next number generated by the random function.

Read more ›

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

Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes

Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes

Here’s how you can assign one macro to multiple drawing shapes on your worksheet, and have them all behave uniquely, depending on which shape was clicked.

In the pictured example, four Oval drawing shapes were placed on the worksheet,

Read more ›

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

Tom’s Tutorials For Excel: Listing Subfolders

Tom’s Tutorials For Excel: Listing Subfolders

I previously posted this example to list files kept in a parent folder.

Today’s example shows how you can list the subfolder names belonging to a parent folder.

Here’s the macro that does this:

Sub ListSubfolders()

‘Declare and define variables.

Read more ›

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

Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder

Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder

Here’s a macro to list the names of all your Excel workbooks from a specified folder into column A of your worksheet.

In the first picture, you see several different types of files in the selected folder.

Read more ›

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

Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells.

Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells.

I previously posted this example for highlighting the selected cell.

Today, here is how you can highlight not only the current cell, but the cell you’d selected before you selected your current cell.

Read more ›

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

Tom’s Tutorials For Excel: Deleting Duplicate Records

Tom’s Tutorials For Excel: Deleting Duplicate Records

When you have a list of data, sometimes it is not enough to simply delete rows with duplicated information based only on the items in one column. Multi-column lists can have duplicated records based on the fact that every item in every column of a row’s data matches that of another row’s entire data,

Read more ›

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

Tom’s Tutorials For Excel: Identifying Your Active Window’s Top Left Cell

Tom’s Tutorials For Excel: Identifying Your Active Window’s Top Left Cell

Here are a few macros that identify the top left cell of your active window’s spreadsheet.

The first example relates to this picture, where cell HX63524 happens to be the top left cell in the active window.

Read more ›

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

Tom’s Tutorials For Excel: Populating TextBoxes From Last Row Upwards

Tom’s Tutorials For Excel: Populating TextBoxes From Last Row Upwards

When you have a list and you want to populate userform TextBoxes for, say, the last 10 records of the items in that list, here’s how.

In the above picture,

Read more ›

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

Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Here is how you can automatically update a comment that shows unique items in sorted order from the larger worksheet list, whenever a new unique item is added to that list in the worksheet.

In the pictured example,

Read more ›

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

Tom’s Tutorials For Excel: Using an InputBox to Find a Row, Column, and Address

Tom’s Tutorials For Excel: Using an InputBox to Find a Row, Column, and Address

This blog entry shows many examples, such as…
• Include a default entry in an InputBox.
• Trap the Cancel and OK buttons in an InputBox.
• Test to find an InputBox entry in a worksheet cell.

Read more ›

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

Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Sometimes you want options in your VBA arsenal for requests to show results in ad hoc fashions.

In the picture, a table has several columns, each containing a varying count of numeric entries.

Read more ›

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

Tom’s Tutorials For Excel: Verifying Your Internet Connection

Tom’s Tutorials For Excel: Verifying Your Internet Connection

With the help of an API declaration, here’s a method for verifying if the active workbook’s system is connected to the internet.

Thanks to MS Excel MVP Mike Alexander at Datapig Technologies for his help with this to include wireless connections in the verification process.

Read more ›

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

Tom’s Tutorials For Excel: Inserting a Picture at a Specific Cell on Multiple Worksheets

Tom’s Tutorials For Excel: Inserting a Picture at a Specific Cell on Multiple Worksheets

Suppose you have a workbook, with several worksheets that often require your company logo to be inserted at a particular cell’s location. Assuming…
• You want the picture at the top left corner of cell D3.

Read more ›

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

Tom’s Tutorials For Excel: Mousing Over a Cell to Run a Macro

Tom’s Tutorials For Excel: Mousing Over a Cell to Run a Macro

Here is how you can trigger a macro by mousing over a range of cells.

This is accomplished without any assistance whatsoever from ActiveX controls or any embedded objects. The cells being moused over (not selected,

Read more ›

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

Tom’s Tutorials For Excel: Exporting Code to Find and Replace

Tom’s Tutorials For Excel: Exporting Code to Find and Replace

Following up last week’s blog entry about exporting code from a module to a text file, here is how to create a copy of that text file and make changes to the code for Find and Replace.

Read more ›

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

Tom’s Tutorials For Excel: Exporting VBA Module Code to a Text File

Tom’s Tutorials For Excel: Exporting VBA Module Code to a Text File

Here’s a quick way to export all the VBA code from a workbook module, assuming for example:
• The module of interest is named Module1.
• The text file that will receive this code is named “YourFileName.txt”.

Read more ›

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

Tom’s Tutorials For Excel: Parsing Data To Create and Populate Separate Workbooks

Tom’s Tutorials For Excel: Parsing Data To Create and Populate Separate Workbooks

Sometimes you need to organize a large table of data by creating and populating individual workbooks based on rows belonging to each primary subject item.

For example, the next picture shows a table of company Stores and their activities.

Read more ›

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

Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array

Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array

Here’s a “this way or that way” pair of macros that use an array to hold a set of items to determine which rows you want to keep or delete. In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Concatenating Multiple Items For Unique Partners

Tom’s Tutorials For Excel: Concatenating Multiple Items For Unique Partners

Here’s how you can take several associated items that are located in their own cells, and concatenate them as a single string to be housed into one cell.

In the picture, you see Primary Items in column A,

Read more ›

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

Tom’s Tutorials For Excel: What time is it *Really*? Ask the US Navy With a Web Query

Tom’s Tutorials For Excel: What time is it *Really*? Ask the US Navy With a Web Query

What is today’s date, and what is the current time of day? Here, you create a Web query to import a display of the current day and time for several North American time zones.

Read more ›

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

Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Here’s a macro to locate the first and last rows and columns, and the ranges being occupied by your pivottable. The (0, 0) notation after the Address property statements is to omit the look of absolute “$”

Read more ›

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

Tom’s Tutorials For Excel: Using an InputBox and Looping Dates for Copy and Sum

Tom’s Tutorials For Excel: Using an InputBox and Looping Dates for Copy and Sum

In January I posted this example for using AutoFilter for dates. AutoFilter is usually quicker and more efficient but I wanted to show how an example of looping through dates by their year.

Read more ›

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

Tom’s Tutorials For Excel: Filtering Automatically On Cell Entry

Tom’s Tutorials For Excel: Filtering Automatically On Cell Entry

Here’s a developer’s approach to make things easy for your users who need to filter a range of data, based on a value they enter in a criteria cell. A Worksheet_Change event is utilized on a worksheet that is designed for this purpose,

Read more ›

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

Tom’s Tutorials For Excel: Why Reset ScreenUpdating to True? Here’s Why!

Why Reset ScreenUpdating to True? Here’s Why!

Occasionally a parroted claim will pop up in the Excel cybersphere that resetting ScreenUpdating from False back to True in a VBA procedure is pointless and unnecessary. Supporters of this claim mistakenly believe that VBA “resets itself” at the end of a macro or procedure,

Read more ›

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

Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros

Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros

Download this example of a custom right-click menu to list and run your macros.

I received an intriguing request on Twitter from a follower named Brandi Leath,

Read more ›

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

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Here’s a method for looping through possible named ranges, and if they exist, compiling a unique list of items found among all those ranges.

I have the word “possible” here as a design wrinkle to demonstrate an option of convenience.

Read more ›

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

Tom’s Tutorials For Excel: Preventing a Workbook From SaveAs

Tom’s Tutorials For Excel: Preventing a Workbook From SaveAs

When you want to keep the name of your workbook the same, without the ability to save it as another name, here’s an example of how to kinda sorta accomplish that.

“Kinda sorta”? Three things in life are certain: death,

Read more ›

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

Tom’s Tutorials For Excel: Deselecting All OptionButtons and CheckBoxes

Tom’s Tutorials For Excel: Deselecting All Embedded OptionButtons and CheckBoxes

If you have embedded OptionButtons or CheckBoxes onto your worksheet from the ActiveX Toolbox or the Forms toolbar, here are two macros for examples of how to deselect those controls. Notice how simple the Forms controls are to manipulate…no looping needed,

Read more ›

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

Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Here’s how to transfer multiple selected items from an ActiveX ListBox onto your spreadsheet without intervening empty cells.

Private Sub cmdConfirm_Click()
‘Turn off ScreenUpdating.
Application.ScreenUpdating = False

‘Declare variables for row and ListBox item index.

Read more ›

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

Tom’s Tutorials For Excel: Emptying the Recycle Bin

Tom’s Tutorials For Excel: Emptying the Recycle Bin

This macro with API empties the Recycle Bin.

Recycle
In a new fresh standard module:

Declare Function EmptyRecycleBin _
Lib “shell32.dll” Alias “SHEmptyRecycleBinA” _
(ByVal hwnd As Long, _
ByVal pszRootPath As String,

Read more ›

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

Tom’s Tutorials For Excel: Creating a Right Click Event For ActiveX Controls

Tom’s Tutorials For Excel: Creating a Right Click Event For ActiveX Controls

Free and safe downloadable workbook example.

There is no built-in menu for the right-click event of ActiveX objects. Here is a utility for that, using a CommandButton as an example.

Read more ›

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

Tom’s Tutorials For Excel: Getting Attention With Multiple Beeps

Tom’s Tutorials For Excel: Getting Attention With Multiple Beeps

Let’s face it, sometimes a warning needs a little more emphasis than the usual single Beep. There are some sound wave files already built into your computer that can be called on, but for today, here’s a simple method for sounding several Beeps instead of just the usual single Beep.

Read more ›

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

Tom’s Tutorials For Excel: Reading the Registry for Your Email (non-Web Mail) Client

Tom’s Tutorials For Excel: Reading the Registry for Your Email (non-Web Mail) Client

Here’s a macro that reads your Registry and tells you the name of your email client. This does not include web mail such as accounts in Gmail and Yahoo.

My email client is Microsoft Outlook.

Read more ›

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

Tom’s Tutorials For Excel: Adding a Cost Increase On Entry

Tom’s Tutorials For Excel: Adding a Cost Increase On Entry

With a WorksheetChange event you can enter a number in a cell, and do an immediate calculation upon entry of that number to display a more meaningful number.

Suppose you are entering prices of items to purchase,

Read more ›

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

Tom’s Tutorials For Excel: Inserting an Empty Row Between Each Change in Value

Tom’s Tutorials For Excel: Inserting an Empty Row Between Each Change in Value

Here’s how you can insert a row between changes in items in your table range. In the picture, you prefer your data to be sorted by Client Name in column A. To make the finished table more readable,

Read more ›

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

Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year

Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year

Companies and organizations often keep a list of their members’ birth dates, for purposes of sending out birthday greetings or managing employees’ birthdays with a complimentary day off work.

As the above picture shows,

Read more ›

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

Tom’s Tutorials For Excel: Hyperlinking a CommandButton to the Internet

Tom’s Tutorials For Excel: Hyperlinking a CommandButton to the Internet

When you have an ActiveX CommandButton on a UserForm or embedded onto your worksheet, the FollowHyperlink method can take you to your favorite website.

In the above picture, the green CommandButton’s caption is the literal URL address.

Read more ›

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

Tom’s Tutorials For Excel: Populating and Sorting Unique Items in a ListBox or ComboBox

Tom’s Tutorials For Excel: Populating and Sorting Unique Items in a ListBox or ComboBox

There are times when you need to populate a ListBox or ComboBox with unique items from a dynamic range containing many repeated items. You’ll also want the items to be sorted automatically,

Read more ›

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

Tom’s Tutorials For Excel: Filtering Dates

Tom’s Tutorials For Excel: Filtering Dates

When it comes to filtering dates, a little VBA goes a long way in dealing with the nemesis of seemingly countless different formats a date can be represented in Excel.

In the picture, an obnoxious myriad of dates is shown in the Date column to demonstrate how,

Read more ›

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

Tom’s Tutorials For Excel: Listing Your Worksheets

Tom’s Tutorials For Excel: Listing Your Worksheets

Here are a few ways to list the names of the worksheets in your workbook.

For starters, you can right-click on the sheet navigation buttons to the left of your sheet tabs, and a list of sheets will appear.

Read more ›

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

Tom’s Tutorials For Excel: Listing and Verifying Fonts

Tom’s Tutorials For Excel: Listing and Verifying Fonts

While you can press Alt+O+E to call the Format Cells dialog box and see what fonts are available, with the below macro named ListFonts you can see your fonts on a worksheet all at once, formatted as their font names.

Read more ›

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

Tom’s Tutorials For Excel: Printing Some Sheets, Not Printing Others

Tom’s Tutorials For Excel: Printing Some Sheets, Not Printing Others

In some cases you want an easy way to print only certain sheets from your workbook.
There are two approaches — identifying which sheets to include, or which sheets to exclude.
Below are two macros,

Read more ›

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

Tom’s Tutorials For Excel: Filtering Columns

Tom’s Tutorials For Excel: Filtering Columns
Free and safe downloadable workbook to filter by columns

As you know, Excel only provides a built-in method of filtering for rows by individual columns.

If you want to filter columns by their row headers,

Read more ›

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

Tom’s Tutorials For Excel: Showing or Hiding Specific AutoFilter DropDowns

Tom’s Tutorials For Excel: Showing or Hiding Specific AutoFilter DropDowns

You might want to AutoFilter a range of data, but only have the AutoFilter drop-downs be visible in the header row for some columns and not for others. This is not possible to accomplish manually, but it is possible with VBA.

Read more ›

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