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”.
…
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.
…
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.
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.
…
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,
…
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.
…
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.
…
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.
…
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,
…
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.
…
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.
…
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.
…
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,
…
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.
…
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,
…
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.
…
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.
…
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.
…
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.
…
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,
…
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.
…
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”.
…
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.
…
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,
…
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,
…
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.
…
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 “$”
…
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.
…
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,
…
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,
…
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,
…
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.
…
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,
…
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,
…
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.
…
Tom’s Tutorials For Excel: Emptying the Recycle Bin
This macro with API empties the Recycle Bin.
In a new fresh standard module:
Declare Function EmptyRecycleBin _
Lib “shell32.dll” Alias “SHEmptyRecycleBinA” _
(ByVal hwnd As Long, _
ByVal pszRootPath As String,…
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.
…
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.
…
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.
…
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,
…
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,
…
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,
…
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.
…
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,
…
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,
…
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.
…
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.
…
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,
…
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,
…
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.
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
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 ›
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Comments / Screen Tips, Data Science, Duplicates Uniques, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis