ExcelTips: The Macros (Table of Contents)

Summary: Macros provide a way for you to extend the capabilities of Excel. The key to macros is understanding how VBA works. Here is the table of contents to ExcelTips: The Macros, which provides all the information you need to put macros to work.

ExcelTips: The Macros (Table of Contents)

ExcelTips: The Macros includes 998 pages of valuable macro-oriented tips. The following is the Table of Contents:

Introduction

  • A Few Words about Versions
  • How to Use the Macros in This Book
  • Need a Good Macro Tutorial?
  • Need More Tips?
  • Want to Learn About VBA Programming in Word?
  • Sharing this Document

Creating and Managing Macros

  • Understanding Macros
  • Recording a Macro
  • Relative References when Recording Macros
  • Writing a Macro from Scratch
  • Editing Macros
  • Continuing Macro Lines
  • Excel 2007 VBA Differences
  • Offering Options in a Macro
  • Renaming a Macro
  • Automating Copying Macros
  • Debugging a Macro
  • Friendly and Informative Error Handling
  • Stepping Through a Macro with a Worksheet Visible
  • Macro Runs Slowly, but Steps Quickly
  • Macro, while Running, Stops Excel from Responding
  • Macros Run Fine Individually, but Not Collectively
  • Getting Big Macros to Run
  • Working while a Macro is Running
  • Assigning a Macro to a Keyboard Combination
  • One Shortcut for Two Macros
  • Removing a Macro from a Shortcut Key
  • Relative VBA Selections
  • Running Macros in the Background
  • Aborting a Macro and Retaining Control
  • Develop Macros in Their Own Workbook
  • Macros in Template Files
  • Forcing a Macro to Run when a Worksheet is Recalculated
  • Deleting a Macro
  • Generating a List of Macros
  • Clean Up Your Macro List
  • Understanding Phantom Macros
  • Getting Rid of the "Enable Macros" Notice
  • Removing All Macros
  • Easily Deploying Customizations
  • Library Not Registered Error
  • Hiding Macros
  • Disabled Macros
  • Maximum Length Limit for a Macro
  • Excluding a Specific Add-In at Startup
  • Using Macros in Protected Workbooks
  • Ctrl+Break Won't Work to Stop a Macro
  • Recovering Macros from Corrupted Workbooks
  • Trouble Recording Paste Special Formula
  • Digital Signatures for Macros
  • Moving Macros from the Personal Workbook
  • Automatically Hiding the Personal Workbook
  • Upgrading a Personal.xls Workbook
  • Opening Personal.xlsb in Excel 2007
  • Documenting Changes in VBA Code
  • Measuring Efficiency of Formulas and Macros
  • Out of Memory Errors when Accessing the VBA Editor
  • Automatically Changing References to VBA Libraries
  • Item Not Available in Library
  • Automatically Opening Macro Workbooks when Using a Shortcut Key

Functions and Subroutines

  • Understanding Subroutines
  • Understanding Functions
  • Making Common Functions Available
  • Worksheet Events
  • Workbook Events

Structures Used in Macros

  • Understanding the For ... Next Structure
  • Exiting a For ... Next Loop Early
  • Understanding the If ... End If Structure
  • Understanding the While...Wend Structure
  • Understanding the Select Case Structure

Working with Variables

  • Understanding Variables in Macros
  • Declaring Variables
  • Swapping the Values in Two Variables
  • Comparing Strings
  • Converting Numbers to Strings
  • Converting Strings to Numbers
  • Quickly Dumping Array Contents

Commands and Statements

  • Deriving an Absolute Value in a Macro
  • Determining a Random Value
  • Determining an ANSI Value in a Macro
  • Determining the Hour of the Day
  • Determining the Day of the Month
  • Determining Differences Between Dates
  • Creating a String in a Macro
  • Dissecting a String
  • Trimming Spaces from Strings
  • Determining the Length of a String
  • Determining an Integer Value
  • Error Using ATAN2 Function in Macro
  • Using BIN2DEC In a Macro
  • Using SUM In a Macro
  • Large Numbers in the MOD Function
  • EOMONTH Function is Flakey
  • Making VLOOKUP Trigger a Macro
  • Making VLOOKUP Case Sensitive

Controlling Data Entry

  • Requiring Input
  • Don't Allow Empty Cells
  • Forcing Input to Uppercase
  • Controlling Entry Order on Unprotected Cells
  • Accepting Only a Single Digit
  • Setting a Length Limit on Cells
  • Entering Data as Thousands
  • Automatically Adding 20% to an Entry
  • Creating a Shortcut for Pasting Values
  • Automatically Protecting After Input
  • Locking All Non-Empty Cells
  • Maintaining the Active Cell
  • Unique Name Entry, Take Two
  • Inserting a Radical Symbol
  • Checking for Proper Entry of Array Formulas
  • Positioning a Column on the Screen
  • Jumping to the Start of the Next Data Entry Row
  • Limiting Who Can Delete Data

Selecting Cells and Ranges

  • Magnifying Only the Current Cell
  • Displaying the Selected Cell's Address
  • Selecting a Cell in the Current Row
  • Selecting a Range of Cells Relative to the Current Cell
  • Selecting the First Cell In a Row
  • Selecting a Specific Cell in a Macro
  • Highlighting the Rows of Selected Cells
  • Choosing Direction After Enter On a Workbook Basis
  • Automatically Moving from Cell to Cell when Entering Data
  • Using a Macro to Select a Modified Table Body

Affecting Worksheet Data

  • Inserting Worksheet Values with a Macro
  • Filling a Range of Cells with Values
  • Copying Named Ranges
  • Copying to Very Large Ranges
  • Clearing Everything Except Formulas
  • Converting Text Case
  • Converting Cells to Proper Case
  • Modifying Proper Capitalization
  • Capitalizing Just a Surname
  • Converting Text to Numbers
  • Converting Imported Information to Numeric Values
  • Reversing Integer Values
  • Converting to ASCII Text
  • Getting Rid of 8-Bit ASCII Characters
  • Creating a Plus/Minus Button
  • Finding the Lowest Numbers
  • Finding the Smallest Even Value
  • Finding Odd Values Greater Than 50
  • Converting from Relative to Absolute
  • Reversing Cell Contents
  • Reversing Names In Place
  • Concatenating Ranges of Cells
  • Concatenating Values from a Variable Number of Cells
  • Finding the First Non-Digit in a Text Value
  • Identifying Digit-Only Part Numbers Excluding Special Characters
  • Finding the Nth Occurrence of a Character
  • Extracting a Pattern from within Text
  • Shortcut to Merge Cells
  • Identifying Merged Cells
  • Getting Rid of Spaces in Cells
  • Removing Duplicate Cells
  • Deleting Duplicate Text Values
  • Removing Duplicates Based on a Partial Match
  • Deleting Everything Up to a Character Sequence
  • Extracting First and Last Words
  • Removing Cells from a Selected Range
  • Extracting Numbers within a Range
  • Deleting Old Data from a Worksheet
  • Deleting Unwanted Styles
  • Conditional Page Breaks
  • Moving Subtotals
  • Formatting Subtotal Rows
  • Determining Combinations to Make a Total
  • Listing Combinations
  • Putting Addresses on State-Specific Worksheets
  • Selectively Importing Records
  • Importing Huge Data Files
  • Merging Many Workbooks
  • Importing Multiple Files to a Single Workbook
  • Getting Rid of Extra Quote Marks in Exported Text Files
  • Getting Rid of Alphabetic Characters
  • Adjusting Values with Formulas
  • Returning Zero When a Referenced Cell is Blank
  • Counting Commas in a Selection
  • Counting Consecutive Negative Numbers
  • Counting Cells Containing a Formula
  • Summing Digits in a Value
  • Summing Absolute Values
  • Summing Based on Formatting in Adjacent Cells
  • Summing Based on Part of the Information in a Cell
  • Summing Only Visible Values
  • Averaging without Hidden Cells
  • Displaying a Count of Zeros on the Status Bar
  • Inserting Dashes between Letters and Numbers
  • Removing Spaces
  • Adding Spaces in Front of Capital Letters
  • Replacing Tildes at the Beginning of a Cell
  • Generating Random Strings of Characters
  • Generating Unique, Sequential Names
  • Randomly Assigning Names to Items
  • Pulling Apart Characters in a Long String
  • Breaking Up Variable-Length Part Numbers
  • Moving the House Number to Its Own Cell
  • Splitting Text to Multiple Cells
  • Splitting Cells by Case
  • Adding Dashes between Letters
  • Removing Dashes from ISBN Numbers
  • Adding a Missing Closing Bracket
  • Pulling Initial Letters from a String
  • Making PROPER Skip Certain Words
  • Determining "Highest Since" or "Lowest Since"
  • Making Changes in a Group of Workbooks
  • Three-Dimensional Transpositions
  • Putting an X in a Clicked Cell
  • Deleting Everything Except Formulas
  • Getting Rid of Everything Except Numbers
  • Deleting Zero Values from a Data Table
  • Removing Cells Containing Specific Terms
  • Number of Terms in a Formula
  • Finding the Number of Significant Digits
  • Leaving a Cell Value Unchanged If a Condition Is False
  • Copying Data without Leaving the Currently Selected Cell

Affecting Cell Formatting

  • Adding Drop Shadows to Cells
  • Highlighting Values in a Cell
  • Creating a Center Across Selection Button
  • Making a Cell's Contents Bold or Italic
  • Counting Cells with Text Colors
  • Official Color Names in VBA
  • Working with Colors in a Macro
  • Showing RGB Colors in a Cell
  • Determining the RGB Value of a Color
  • Replacing Background Colors in Cells
  • Creating Superscript and Subscript Buttons
  • Automatically Copying Formatting
  • Matching Formatting when Concatenating
  • Finding Positions of Formatted Characters in a Cell
  • Checking All Cell Formatting in VBA
  • Replacing Cell Formats
  • Determining Font Formatting
  • Changing Fonts in Multiple Workbooks
  • Making All Occurrences Bold
  • Select One Cell and Make Another Cell Bold
  • Separating Cells Based on Text Color
  • Shortcut Key for Format Painter
  • Finding Cells Filled with a Particular Color
  • Counting Colors of Cells
  • Counting Empty Colored Cells
  • Using COUNTIF with Colors
  • Flashing Cells
  • Automatically Formatting for Decimal Places
  • Using a Custom Format to Add Dashes
  • Moving Custom Formats to Number Formatting Categories
  • Coloring Cells with Formulas
  • Coloring Identical Company Names
  • Colors in an IF Function
  • Conditionally Highlighting Cells Containing Formulas
  • Conditionally Formatting Cells Containing Dates
  • Conditional Formats for Odd and Even Columns
  • Conditional Formats that Distinguish Blanks and Zeroes
  • Changing Font Face and Size Conditionally
  • Diagonal Borders in a Conditional Format
  • Protecting Your Conditional Formatting Rules
  • Detecting Errors in Conditional Formatting Formulas
  • Removing Conditional Formats, but Not the Effects
  • Changing Currency Formatting for a Single Workbook
  • Changing Number Display Settings for Single Workbooks

Affecting Rows and Columns

  • Removing Duplicate Rows
  • Conditionally Deleting Rows
  • Deleting Every X Rows
  • Hiding Rows Based on a Cell Value
  • Hiding Rows Based on Two Values
  • Detecting Hidden Rows
  • Skipping Hidden Rows in a Macro
  • Easily Adding Blank Rows
  • Inserting and Copying Rows
  • Moving and Selecting Rows
  • Setting Row Height in a Macro
  • Adjusting to a Maximum Row Height
  • Automatic Row Height for Wrapped Text
  • Adjusting Row Height for a Number of Worksheets
  • Splitting Information into Rows
  • Shading Rows for Ease in Reading Output
  • Summing Every Fourth Cell in a Row
  • Condensing Sequential Values to a Single Row
  • Hiding a Huge Number of Rows
  • Getting Rid of Empty Rows after Importing
  • Floating Information in a Frozen Row
  • Rows in a PivotTable
  • Alphabetic Column Designation
  • Getting Rid of Numbered Columns
  • Unhiding a Single Column
  • Setting Column Width in a Macro
  • Widening Multiple Columns Proportionally
  • Changing Width and Height to Inches
  • Deleting Duplicate Columns
  • Deleting Blank Columns
  • Combining Columns
  • Combining Multiple Rows in a Column
  • Changing Shading when a Column Value Changes
  • Selecting Columns in VBA when Cells are Merged
  • Hiding Columns Based on a Cell Value

Filtering Data and Working with Filtered Data

  • Toggling AutoFilter
  • Quickly Identifying Applied AutoFilters
  • Enabling Filters by Default
  • Filtering for Comments
  • Clearing Only Filtering Settings
  • Changing AutoFilter Drop-Down Arrow Colors
  • Column Formatting Based On a Filter
  • Easy Filtering Specifications for a PivotTable
  • Removing Filters and Unhiding Rows and Columns on Multiple Worksheets
  • Macro Fails after AutoFilter

Working with Dates and Times

  • Modifying Default Year for Dates
  • Entering Dates without Separators
  • Parsing Non-Standard Date Formats
  • Converting an Unsupported Date Format
  • Deciphering a Coded Date
  • Limiting Entry of Prior Dates
  • Adjusting Date Values by Keypress
  • Preventing Automatic Date Formatting Changes
  • Pushing Dates Into Last Month
  • Deleting Dates within Text Strings
  • Converting Dates to Text
  • Hiding Columns Not within a Date Range
  • Determining If a Year is a Leap Year
  • Getting Excel Dates into Outlook's Calendar
  • Adding Ordinal Notation to Dates
  • Tombstone Date Math
  • Finding the Date Associated with a Negative Value
  • Automatically Advancing by a Month
  • Weekdays in a Month
  • Pulling All Fridays
  • The Last Business Day
  • Expiration Date for Excel Programs
  • Installing the Date Picker
  • Inserting the Current Time with Seconds
  • Recording a Data Entry Time
  • Automatically Entering a Data Entry Time
  • Entering Large Time Values
  • Checking for Time Input
  • Entering or Importing Times without Colons
  • Shortcut to Enter GMT
  • Automatically Converting to GMT
  • Using Excel for Timing
  • Calculating TV Time
  • Converting Numeric Values to Times
  • Is Daylight Savings Time in Effect?

Working with Worksheets

  • Creating Worksheets with a Macro
  • Creating and Naming a Worksheet Using a Macro
  • Jumping to a Specific Worksheet
  • Shortcut to Move between Two Worksheets
  • Displaying the First Worksheet in a Macro
  • Opening a Workbook to a Specific Worksheet
  • Telling which Worksheets are Selected
  • Determining a Worksheet's Number
  • Deriving the Worksheet Name
  • Getting the Name of the Worksheet Into a Cell
  • Retrieving Worksheet Names
  • Dynamic Worksheet Tab Names
  • Dynamically Changing Worksheet Tab Color
  • Alphabetizing Worksheet Tabs
  • Ordering Worksheets Based on a Cell Value
  • Copying Worksheets in a Macro
  • Creating a Copy without Formulas
  • Relative Worksheet References
  • Sheets for Days
  • Referencing Worksheet Tabs
  • Referencing a Worksheet Name
  • Locking Worksheet Names
  • Freezing Worksheet Tabs
  • Running Macros on Hidden Worksheets
  • Spell-Checking in a Protected Worksheet
  • Preventing Someone from Recreating a Protected Worksheet
  • Fixing Macro Button Behavior in Protected Worksheets
  • Buttons Don't Stay Put
  • Visually Showing a Protection Status
  • Forcing a Worksheet to be Protected Again
  • Unhiding Multiple Worksheets
  • Detecting Types of Sheets in VBA
  • Selecting All Visible Worksheets in a Macro
  • Deleting Worksheets in a Macro
  • Condensing Multiple Worksheets Into One
  • Combining Worksheets from Many Workbooks
  • Comparing Formulas on Two Worksheets
  • Copying Rows between Worksheets Based on a Text Value
  • Counting the Times a Worksheet is Used
  • Generating Unique Numbers for Worksheets
  • Disabling Moving Between Worksheets
  • Random Width and Height Changes
  • Identifying the Last Cell Changed in a Worksheet
  • Transferring Data between Worksheets Using a Macro
  • Viewing Same Cells on Different Worksheets
  • Testing for an Empty Worksheet
  • Turning Off Display of Zeros for All Worksheets
  • Copying Worksheet Code Automatically
  • Finding the Size of Individual Worksheets

Interacting with the Excel Environment

  • Switching Windows in a Macro
  • Setting Program Window Size in a Macro
  • Dividing the Screen Unevenly between Two Workbooks
  • Remembering Workbook Position and Size
  • Inadvertantly Getting Rid of Frozen Panes
  • Turning Off Screen Updating
  • Disabling Shift Key Use when Opening a Workbook
  • Opening a Workbook but Disabling Macros
  • Forcing Editing to Be Done in a Cell
  • Synchronous Scrolling with More than Two Windows
  • Using Go To to Jump to a Chart Sheet
  • Displaying a Set Column Range
  • Selecting Visible Cells in a Macro
  • Stepping Through a Non-Contiguous Range of Cells
  • Hiding Excel in VBA
  • Preserving the Undo List
  • Clearing the Undo Stack
  • Calculating Only the Active Workbook
  • Setting the Calculation Default
  • Counting All Characters
  • Unhiding or Listing All Objects
  • Using Named Ranges in a Macro
  • Deleting All Names but a Few
  • Default Cell Movement when Deleting
  • Saving Movement on Enter with a Workbook
  • Forcing Manual Calculation For a Workbook
  • Pulling Formulas from a Worksheet
  • Saving Non-Existent Changes
  • Counting Precedents and Dependents
  • Accessing Dependent and Precedent Information
  • Discovering Dependent Workbooks
  • Leaving Trace Precedents Turned On
  • Turning Off AutoFill for a Workbook
  • Noting the Workbook Creation Date
  • Displaying the "Last Modified" Date
  • Noting When a Workbook was Changed
  • Date Last Edited
  • Finding the Path to the Desktop
  • Grabbing a User's Name from Excel
  • Inserting the User's Name in a Cell
  • Adjusting a Path Based on System and User
  • Grabbing the MRU List
  • Seeing All Open Workbook Names
  • Testing if a Workbook is Open
  • Getting Rid of "Copy of"
  • Pulling Cell Names into VBA
  • Simultaneous Scrolling
  • Looking Up Names when Key Values are Identical
  • Determining if Caps Lock is On
  • Disabling Page Layout View
  • Disabling a Function Key
  • Disabling the F1 Key
  • Disabling Excel's Help System
  • Retrieving Drive Statistics
  • Full Path Names in Excel
  • Automatically Closing a Workbook
  • Finding Unknown Links
  • Getting Rid of Workbook Links
  • Replacing Links with Values
  • Generating Automatic Links to Audio Files
  • Task Pane Doesn't Appear Properly
  • Finding the Last-Used Cell in a Macro
  • Jumping to the Real Last Cell
  • Determining if Calculation is Necessary
  • Iterating Circular References
  • Tracking Down Invalid References
  • Determining How Many Windows are Open
  • Saving Common Formulas
  • Turning Off Track Changes without Unsharing
  • Checking if a Workbook is Already Open
  • Saving All Open Workbooks
  • Saving a Workbook in a Macro
  • Finding the Size of a Workbook
  • Finding Workbooks Containing Macros
  • Saving Changes when Closing
  • Closing a Read-Only Workbook
  • Forcing a Workbook to Close after Inactivity
  • Using a Single Password for Multiple Workbooks
  • Finding Other Instances of Excel in a Macro
  • Who Has the File Open?
  • Macros Run Slower in Newer Excel?
  • Making Modal Dialog Boxes Appear in Front of Workbooks

The Ribbon, Menus, and Toolbars

  • Displaying Excel's Developer Tab
  • Tools on Developer Tab are Unavailable
  • Adding a Macro to a Toolbar
  • Adding a Macro to the Quick Access Toolbar
  • Controlling Display of Toolbar Buttons
  • Changing the Shortcut Menu
  • Adding Items to a Context Menu
  • Removing Items from a Context Menu
  • Problem with Missing Context Menu Option

Working with Comments

  • Copying Comments to Cells
  • Inserting Workbook Comments Into a Cell
  • Moving Comment Background Pictures to Cells
  • Setting the Default Font Size for Comment Balloons
  • Copying Comments when Filtering
  • Adding Comments to Protected Worksheets
  • Adding a Comment to Multiple Cells
  • Linking Comments to Multiple Cells
  • Editing a Comment Close to Its Cell
  • Finding and Replacing Text in Comments
  • Placing Formula Results in a Comment
  • Counting Comments in a Worksheet
  • Changing the Comment Indicator Color

Working with Headers and Footers

  • Dynamic Headers and Footers
  • Full Path Names in Headers or Footers
  • Using a Formula in a Footer
  • Adding a File Path and Filename
  • Extracting File Names from a Path
  • First and Last Names in a Page Header
  • Multiple Line Headers and Footers
  • Putting a Different Date in a Header
  • Inserting the Saved Date in a Header or Footer
  • Last Saved Date in a Footer
  • Specifying Date Formats in Headers
  • Selective Headers and Footers
  • Switching Headers in a Frozen Row
  • Putting Cell Contents in Footers
  • Copying Headers and Footers
  • Header and Footer Background Color
  • Using a Different Footer on Secondary Pages
  • Leading Zeros in Page Numbers
  • Changing Page Number Format
  • Roman Numerals for Page Numbers
  • Changing Section Headers
  • Find and Replace in Headers

Working with Graphics and Charts

  • Assigning Macros to Graphics
  • Pasting a Graphic to Multiple Worksheets
  • Positioning a Graphic in a Macro
  • Copying Pictures with a Macro
  • Pop-Up Comments for Graphics
  • ScreenTip for an Image
  • Sizing Text Boxes and Cells the Same
  • Resizing a Text Box in a Macro
  • Finding Text in Text Boxes
  • Placing Textbox Text Into a Worksheet
  • Changing Text in Text Boxes on a Chart
  • Adding AutoShapes
  • Shifting Objects Off a Sheet
  • Removing Pictures for a Worksheet in VBA
  • Snapshots of Excel Worksheets for PowerPoint
  • Creating Charts in VBA
  • Automatically Creating Charts for Individual Rows in a Data Table
  • Unlocking Charts
  • Positive and Negative Colors in a Chart
  • Labeling X-Y Scatter Plots
  • Exporting Black and White Charts
  • Specifying the Size of Chart Objects
  • Specifying Chart Sizes
  • Changing Elements in Lots of Charts at One Time
  • Converting Charts to GIF Files
  • Creating a Photo Catalog from a Folder of Photos
  • Hyperlinks to Charts
  • Determining Mouse Cursor Coordinates On a Graphic

Working with the Outside World

  • Getting User Input in a Dialog Box
  • Using InputBox to Get Data
  • Hiding Entries in an InputBox
  • Pausing Macros for User Input
  • Using Message Boxes
  • Conditionally Displaying a Message Box
  • Specifying Location for a Message Box
  • Progression Indicator in a Macro
  • Hiding an Excel 2007 Progress Indicator
  • Using the Status Bar
  • Getting a File Name
  • Determining If a File Exists
  • Checking for the Existence of a File
  • Determining the Length of a Text File
  • Getting Input from a Text File
  • Saving Information in a Text File
  • Appending to a Text File
  • Using Seek In a Macro
  • Renaming a File
  • Deleting a File
  • Faster Text File Conversions
  • Importing a Subset of Records
  • Aligning Cells when Importing from CSV
  • Specifying a Delimiter when Saving a CSV File in a Macro
  • Creating a Directory
  • Changing Directories
  • Determining the Current Directory
  • Removing a Directory
  • Changing the Default Drive
  • Easily Changing the Default Drive and Directory
  • Word Documents from Excel Macros
  • Using Old Lotus Macros
  • Linking to a Specific Page in a PDF File
  • Updating Automatically when Opening Under Macro Control
  • Displaying Messages when Automatic Data Changes
  • Preparing Data for Import into Access
  • Getting Contact Information from Outlook
  • Creating a Dated Backup File

Web and Online

  • Opening Sites in a Browser
  • Opening an HTML Page in a Macro
  • Specifying a Browser in a Hyperlink
  • Converting a Range of URLs to Hyperlinks
  • Pulling Apart a URL
  • Specifying Default Hyperlink Text
  • Showing Visited Hyperlinks
  • Getting Rid of Many Hyperlinks
  • Getting Rid of All Hyperlinks
  • Changing Portions of Many Hyperlinks
  • Changing Huge Numbers of Hyperlinks
  • Converting to Hyperlinks in a Shared Workbook
  • Extracting E-mail Addresses from Hyperlinks
  • Extracting URLs from Hyperlinks
  • Extracting URLs from Hyperlinked Images
  • Extracting Hyperlink Information
  • Get Rid of Web Stuff
  • Sending Single Worksheets via E-mail
  • Sending an E-mail when a Due Date is Reached
  • Suppressing the Reviewing Toolbar on E-mailed Workbooks
  • Retrieving Web Query Data without Interruption
  • Hiding a Hyperlink on a Printout

Working with the Printer

  • Page Numbers in VBA
  • Custom Page Numbers on Printouts
  • Specifying the Y Value in X of Y Page Numbering
  • Changing Paper Size for a Complete Workbook
  • Displaying the Print Dialog Box in a Macro
  • Specifying Print Quantity in a Cell
  • Controlling the Printer in a Macro
  • Working with Multiple Printers
  • Using Multiple Print Settings
  • Can Only Print to Default Printer
  • Printing Selected Worksheets
  • Printing Only Non-Blank Worksheets
  • Printing Limited Pages from a Range of Worksheets
  • Forcing Worksheets to Print on a New Sheet
  • Printing Odd or Even Pages
  • Printing an Entire Workbook by Default
  • Automatic Selection of Portrait or Landscape
  • Printing a Single Column in Multiple Columns
  • Printing Multiple Worksheets on a Single Page
  • Printing a Worksheet List
  • Printing Workbook Properties
  • Adjusting Comment Printouts
  • Disabling the Print Option
  • Automatically Printing a Range
  • Setting Print Ranges for Multiple Worksheets
  • Printing All or Nothing
  • Conditional Printing
  • Protecting Print Settings
  • Locking the Print Area
  • Showing Filter Criteria on a Printout
  • Repeating Rows on a Printout Except On the Last Page
  • Printing Based on Cell Contents
  • Changing Cell Values while Printing

Special-Purpose Macros

  • Running a Macro when a Workbook is Opened
  • Running a Macro when a Workbook is Closed
  • Running a Macro when a Worksheet is Activated
  • Running a Macro when a Worksheet is Deactivated
  • Triggering an Event when a Worksheet is Deactivated
  • Opening a Workbook and Suppressing Automatic Macros
  • Always Opening a Workbook that is Editable
  • Running a Macro in a Number of Workbooks
  • Mouse Click Event in VBA
  • Saving in Two Locations
  • Saving in Multiple Locations
  • Understanding Add-Ins
  • Creating Add-Ins
  • Using Custom Add-Ins
  • Automatically Loading Add-ins

Searching and Sorting

  • Universal Searching
  • Searching for All
  • Searching through Many Workbooks
  • Searching a Workbook by Default
  • Searching by Columns, by Default
  • Changing Default Search Settings
  • Searching for Leading Apostrophes
  • Searching for Line Breaks
  • Searching for a Value Using a Function
  • Where Is that Text?
  • Using Find and Replace to Pre-Pend Characters
  • Replacing Only Whole Words in Excel
  • Replacing Characters at the End of a Cell
  • Finding and Replacing with Subscripts
  • Finding and Replacing Error Values
  • Finding and Replacing in Text Boxes
  • Superscripts in Find and Replace
  • Wildcards in 'Replace With' Text
  • Replacing in Worksheets and Comments At the Same Time
  • Finding Columns of a Certain Width
  • Sorting Worksheets
  • Non-standard Sorting
  • Sorting Data on Protected Worksheets
  • Sorting by Colors
  • Sorting by Fill Color
  • Sorting Data Containing Merged Cells
  • Determining Sorting Criteria
  • Storing Sorting Criteria
  • Automatically Sorting as You Enter Information

Macro Cookbook

  • AutoFilling with the Alphabet
  • Converting Numbers Into Words
  • Converting Phone Numbers
  • Adding Leading Zeroes to ZIP Codes
  • Shortening ZIP Codes
  • Working with Imperial Linear Distances
  • Calculating the Distance between Points
  • Selecting Random Names
  • Generating Random Testing Data
  • Zooming with the Keyboard
  • Always Open at 100% Zoom
  • Determining If a Number is Odd or Even
  • Playing with a Full Deck
  • Counting Wins and Losses
  • Making Squares
  • Counting Words
  • Default Worksheet when Opening
  • Protecting Individual Worksheets, by User
  • Unprotecting Groups of Worksheets
  • Creating Individual Workbooks
  • Disappearing Toolbar Buttons for Macros
  • Sheets for Months
  • Macro for Month Name
  • Naming Tabs for Weeks
  • Jumping to Alphabetic Worksheets
  • Flipping Data
  • Counting Shaded Cells
  • Counting Unique Values
  • Counting Cells According to Case
  • Only Showing the Maximum of Multiple Iterations
  • Finding Differences between Lists
  • Highlighting Cells Containing both Letters and Numbers
  • List of Macro Shortcuts in All Open Workbooks
  • Quickly Changing Windows
  • Using an Exact Number of Digits
  • Engineering Calculations
  • Maintaining Accuracy of Significant Digits
  • Counting Groupings Below a Threshold
  • Referencing External Cell Colors
  • Automatically Editing Formulas
  • Developing Reciprocal Conversion Formulas
  • Spreading Out a Table
  • DOS from Macros
  • Deleting Macros from within a Macro
  • Self-Deleting Macros
  • Deleting Worksheet Code in a Macro
  • Changing Macro Cell References Based on Edits
  • Using the Camera in VBA
  • Conditional Page Breaks
  • Calculating the Interval Between Occurrences
  • Self-Aware Macros
  • Pasting without Updating References
  • Jumping To a Specific Page
  • Checking for Either of Two Text Values
  • Updating Multiple PivotTables at Once
  • Removing Subtotals from Many PivotTable Fields
  • Reducing File Sizes for Workbooks with PivotTables
  • Using Classic PivotTable Layout as the Default
  • Displaying a PivotTable's Name in the PivotTable
  • Resizing Checkboxes
  • Stopping a Checked Box from being Unchecked
  • Extracting Street Numbers from an Address
  • Extracting a State and a ZIP Code
  • Combinations for Members in Meetings
  • Replacing Some Formulas with the Formula Results
  • Finding the Address of the Lowest Value in a Range
  • Extracting Proper Words
  • Clearing Large Clipboard Entries
  • Pulling Apart Cells
  • Reorganizing Data
  • Finding Unused Names
  • Getting Rid of Non-Printing Characters Intelligently
  • Changing Months in a Workbook
  • Triggering a Macro for Drop-Down List Changes
  • Delimited Text-to-Columns in a Macro
  • Replacing and Converting in a Macro
  • Pulling Filenames into a Worksheet
  • Protecting an Entire Folder of Workbooks
  • Storing a User's Location before Running a Macro
  • Limiting Scroll Area
  • Waiting for Update Completion
  • Conditionally Making a Sound
  • Conditionally Playing an Audio File

Description CD Download
ExcelTips: The Macros Buy on CD
ISBN 978-1-61359-351-6 (998 pages)
$41.99
Buy Download
ISBN 978-1-61359-350-9 (6.2 MB)
$34.99

You can also order ExcelTips: The Macros by calling our order line: 307-200-0450.