Macros Examples for Microsoft Excel

A book of ready-to-use Visual Basic macro examples for Microsoft Excel — order online and get delivery in minutes.

General Interest Topics

• Problems Accessing Visual Basic Help
• Determining the Excel Version
• Country and Language Versions of Excel
• High Security and Enabling Macros
• How to Determine Regional Settings or Properties
• Controlling the Cursor Appearance
• Using the Immediate Window
• Documenting Your Code
• How to Clean Your Code
• Useful Module Level Statements
• Recovering Code from a Corrupt File
• Naming Your Projects
• Docking Windows in the Visual Basic Editor
• Books on Learning Windows API
• Disabling Macro Virus Check
• About the LaRoux Virus

Add-ins and Protecting Your Code

• Creating Add-ins
• Certification of Your Add-ins
• Protecting Your Code from Others
• Running Add-ins
• How to Create XLL's
• Creating COM Add-ins
• Using DLL Functions in Excel
• Problems with Add-ins
• Installing Add-ins via Visual Basic Code
• Using Solver with Visual Basic

Module Examples

• Naming Your Modules and UserForms
• Copying Modules
• Showing Just a Single Procedure
• Removing Modules via Visual Basic Code
• Delete Modules with Code
• Removing All Modules from a Workbook
• Exporting and Importing Modules
• Deleting a Macro via Code
• Listing the Subroutines in a Workbook
• Using a Class Module to Capture Events in Excel
• Declaring a New With Event Class

Variables and Their Use

• Declaring Variables
• Variable Names to Avoid
• Environment Variable Values
• About Local, Module, and Global Variables
• Global or Public Variables in a User Form's Code Module
• Actions That Reset Variables
• Setting Variables to Refer to Cell Ranges
• Sharing Variable Values Between Workbooks
• Resetting or Clearing an Object Variable
• Disabling Toolbar Right Click
• Testing to See If an Object Variable Is Set
• Setting an Object Variable to a Column
• Storing Values in Workbook Names

Arrays Examples

• Determining the Size of an Array
• Passing an Array to a Subroutine
• Clearing Arrays and Object Variables
• How to Get the Unique Entries in a Selection
• Getting a List of the Unique Items in a List
• Storing Range Values in an Array
• Setting Array Size Dynamically
• Copying Array Values to a Range of Cells
• How to Transpose a Range of Values
• Editing Cells the Fast Way
• Sorting an Array
• Writing Arrays to a Worksheet
• Assign Range Values Directly to an Array

Looping and Other Useful Statements

• Using Case Instead of If Tests
• Using a Select Statement to Take Action
• Determining What Type a Value Is
• Using Select as a Multiple Or Statement
• How to Return to Your Starting Location
• Processing All the Entries in a Column
• Some Simple Loop Examples

Cell and Range Examples — Editing, Copying and Pasting

• Copying and Pasting
• Writing Large Numbers to Cells
• A Technique to Avoid
• Writing Text to the Clipboard
• Clearing the Clipboard After a Copy Command
• An Example of How to Copy One Range to Another Range

Cell and Range Examples — Row Examples

• Determining the Currently Selected Cell's Row
• Testing Whether a Row Is Selected
• How to Select All the Rows in a Database
• Selecting Rows Based on Cell Entries
• Select Odd Rows
• How to Determine If a Selection Has Non-Adjacent Areas
• Determining If a Row or Column Is Empty
• Duplicating the Last Row in a Set of Data
• Inserting Multiple Rows
• Insert Rows and Sum Formula When Cells Change
• An Example of Inserting Rows and Sum Formulas
• Deleting Rows
• Deleting Sets of Rows
• Deleting Error Rows
• Deleting Duplicate Rows
• Remove/Highlight Duplicate Rows
• Conditionally Deleting Rows
• How to Delete Blank Rows
• Examples That Delete Rows Based on a Cell's Value
• Auto Sizing Rows When Cells Are Merged

Cell and Range Examples — Column Examples

• Making Certain That a Selection Is Only a Single Column or Row Wide
• Converting Column Letters to Column Numbers
• Converting Alphabetic Column Labels to Numeric Column Labels
• Getting the Letter of a Column
• Comparing Two Columns
• How to Convert Alphabetic Column Labels to Numeric
• How to Copy Multiple Columns at a Time
• How to Delete Columns in Multiple Sheets at One Time
• How to Insert Columns in Multiple Sheets at One Time
• An Insert a Column and Formula Example
• Deleting Columns
• Setting Column Widths
• Setting Column Widths and Row Heights
• Setting Column Widths to a Minimum Width
• Setting Column Width and Row Height in Centimeters
• Determining the Populated Cells in a Column of Data

Cell and Range Examples — Finding the First Blank Cell

• Determining Where the First Blank Is in a Column
• Finding the First Blank Cell in a Column
• How to Find the Next Available Row in Column

Cell and Range Examples — Selecting the Last Cell

• The VBA Equivalents of Ctrl Keys
• Determining the Last Cell in a Column
• Finding the Last Entry in a Column
• Finding the Last Non-Blank Cell
• Finding the Last Entry in a Row
• Determining the Last Cell in a Row
• Finding the Last Cell, Last Row, or Last Column
• Selecting from the ActiveCell to the Last Used Cell
• Determining the Last Cell When Multiple Areas Are Selected

Cell and Range Examples — Color and Format Examples

• Color Every Other Row Gray and Bold Text
• Coloring Cells Based on Their Value
• Coloring Cells Example
• Copying Formats from One Sheet to Another
• Summing Cells Based on Cell Color
• Outlining a Selection
• Getting the Formatted Contents of a Cell

Cell and Range Examples — Working with Formulas

• Writing Formulas That Require Double Quotes
• The Difference Between Formula and FormulaR1C1
• Modifying a Cell's Formula
• Determining If a Cell Contains a Formula

Cell and Range Examples — Working with Comments

• Checking for Comments
• Commenting a Cell with a Macro
• Working with Comments
• How to Create or Append a Comment on a Cell
• Deleting Comments
• Auto-Sizing Comments

Cell and Range Examples — Cell Examples

• Determining What Is in a Cell
• Determining Information About a Cell
• Reading and Writing Cell Values Without Switching Sheets
• Determining If a Cell Is Empty and Problems with IsEmpty
• Testing to See If a Cell Is Empty
• Assigning a Value to a Cell
• Using Visual Basic to Extract Data from Cells
• Copying Values Without Using Paste Special
• Checking for Division by Zero
• Filling a Range with a Formula
• Changing the Value of Cells in a Range Based on Each Cell's Value
• Undoing the Last Manual Entry
• Determining the Number of Selected Cells
• How to Determine If a Range Is Empty
• Determining the Number of Empty Cells in a Range
• Cell References and Merge Cells
• Determining the Number of Cells with Entries
• Modifying Cell Values Based on Two Tests
• VBA Code for ALT-Enter

Cell and Range Examples — Selecting and Specifying Cells

• How to Reference the Selected Cells
• Specifying Cells Relative to Other Cells
• Referring to Cells and Ranges
• Using the Offset Function to Specify Cells
• Use the Offset Method to Specify Cells Relative to Other Cells
• Scrolling to a Particular Cell
• Controlling Cell Selection and the Scroll Area
• Selecting a Range for Sorting or Other Use
• Making Certain That a Selection Consists of Only a Single Area
• Counting and Selecting Cells with Certain Characteristics
• How to Expand or Resize a Range
• Resizing or Expanding a Range
• Selecting Just Blank Cells
• Selecting Just Number Cells
• Selecting the Current Region
• Using the UsedRange Property in Your Code
• Resetting the UsedRange
• Selecting the Used Range on a Sheet
• Restricting a Selection to the Cells in the Sheet's Used Range
• Using the Intersect Method with Ranges
• Getting the Intersection of Two Ranges
• Union Method Problem
• Limiting Access to Cells
• Hiding the Cursor Frame / Preventing Cell Selection
• Preventing Cell Drag and Drop
• Using the Merge Command in Your Code
• VBA and Validation List

Cell and Range Examples — Determining If a Range Is in Another Range

• Determining If a Selection Is Within a Named Range
• Determining If a Range Is Within a Specific Range
• Determining If One Range Is Within Another
• Determining If a Cell Is Within a Certain Range
• Determining When a Cell Is Within a Range
• How to Determine If the ActiveCell Is Within a Named Range
• A Function That Determines If a Range Is Within Another Range

Cell and Range Examples — Working with Range Names

• Working with Range Names
• Creating Range Names
• Creating Hidden Range Names
• Referring to a Range Name in Your Code
• How to Refer to Range Names in Your Code
• Check for Existence of a Range Name
• Determining If a Range Has Been Assigned a Range Name
• Determining the Name Assigned to a Cell
• Expanding a Range Name's Range
• Accessing a Named Range's Value in Another Workbook
• Deleting Range Names
• Deleting All the Range Names in a Workbook
• Deleting Bad Range Names with a Macro

Text and Numbers Examples

• 255 Character Limitations
• Adding Characters to the End of a String
• Adding Text to a Range of Cells
• Case Insensitive Comparisons
• Concatenating Strings
• Converting Numbers That Appear as Text Back to Numbers
• Converting Numbers to Strings
• Converting Text to Proper Case
• Creating a Fixed Length String
• Determining If a Number Is Odd or Even
• Determining If a Value Is Text or Numeric
• Entering Special Characters with the Chr Function
• Extracting Beginning Numbers from Text Strings
• Extracting Numbers from the Left of a String
• Extracting Numbers from the Right Side of a String
• Extracting Part of a String
• Extracting Strings Separated by a /
• Finding the Number of Occurrences of a String in a Range
• How to Get the Number of Characters in a Selection
• How to Test If a Cell or Variable Contains a Particular Text String
• Numbers to Words
• Finding a Font
• Removing Alt-Enter Characters
• Removing Text to the Right of a Comma
• Using the Chr Function to Return Letters
• Using the LIKE Operator to Do Text Comparisons
• Writing the Alphabet Out to a Worksheet

Message Box Examples

• Displaying Message Boxes
• Formatting in a Message Box
• Using Double Quotes in a Message Box
• How to Format a Message in an InputBox or Message Box
• Adding a Help Button to a MsgBox

Input Boxes and Getting User Input

• Pausing a Macro for Input
• Restricting What Is Allowed in an InputBox
• Prompting the User to Enter a Number
• Using the Application InputBox Function to Specify a Number
• InputBox to Ask for the Date
• Using the Visual Basic InputBox to Return a Range
• How to Get a Cell Address from a User
• Using InputBoxes to Get a Cell Range
• An Application InputBox Example That Gets a Range
• Using the InputBox to Put a Value in a Cell
• Prompting the User for Many Inputs

UserForm Examples

• How to Create and Display UserForms
• How to Make UserForms Disappear When They Are Hidden
• UserForm Display Problem
• Initializing User Forms
• Unloading Versus Hiding a UserForm
• Using Hide Instead of Unload with UserForms
• Having UserForms Retain Settings Between Macro Runs
• Setting the Tab Order in a UserForm
• Shortcut Variable Name for a UserForm
• Passing Information and Variables to User Form Procedures
• Passing Values from a Userform to a Sub
• Putting Data on a Sheet from a Userform
• Getting Values from a UserForm
• Displaying a Dialog to Get a Password
• Removing the Quit/X Button on a Userform
• Hiding the Exit X on a Userform
• Disabling the Exit X on a Userform
• Displaying a UserForm Without a Blue Title Bar
• Showing and Getting Values from a User Form
• Making a Userform the Size of the Excel Window
• Showing a Userform for Just a Few Seconds
• Date Validation for UserForm TextBoxes
• Preventing a User from Closing Excel
• Changing the Names of User Form Objects
• Showing Another UserForm from a UserForm
• UserForms Sometimes Reset Modules
• Unreliable Events with UserForms
• RowSource Property Bug
• Force Userform to Top Right of Screen
• Userform Controls
• Accessing a Userform from Another Workbook
• Iterating Through Objects in a Frame
• Looping Through Controls on a Userform
• Converting DialogSheets to UserForms
• Useful Internet Articles on UserForms and DialogSheets

MultiPage Controls

• Specifying the Starting Page in a MultiPage Control
• Setting the Displayed Page of a MultiPage UserForm Object
• How to Add Additional Pages to a MultiPage Tab in a UserForm
• Activating Page on a UserForm's MultiPage

Buttons and Checkboxes

• Putting OK and Cancel Buttons on UserForms
• How to Associate Code with a Button on a User Form
• Making Buttons on UserForms Do What You Want
• Grouping Option Buttons With or Without a Frame
• How to Check How Many CheckBoxes Are Clicked

Using the RefEdit Control

• Using the RefEdit Control on a Userform
• Using a RefEdit Form on a User Form to Select a Range
• Using Reference EditBoxes on DialogSheets
• Sample Code on Using the RefEdit Box

Labels and TextBoxes

• An Example of Using a UserForm with a TextBox
• Highlighting Entry in a Userform TextBox
• How to Select the Entry in a TextBox
• How to Clear and Set TextBox Entries
• Cursor Position in a UserForm TextBox
• How to Format a Number on a Label in a UserForm
• Multiple TextBoxes with Same Validation
• Formatting Textbox Entries
• Formatting TextBoxes on UserForm
• Formatting Numbers in a UserForm Textbox
• Bulk Clearing of Text Boxes
• Validating UserForm Textbox Entries
• Validating UserForm TextBox Input
• Validating a TextBox Entry as a Number
• Automatically Adding Hyphens to Phone Numbers in Text Box
• Forcing a Textbox to Accept Only Numbers
• Reading a Date from a Textbox

Combo, Dropdown and List Boxes

• ListBox Differences
• Populating a ComboBox or ListBox with External Data
• Populating a List Box with Unique Entries
• Assigning a Range to a ListBox
• Linking a List Box on a UserForm to Cells on a Worksheet
• Filling a Listbox with Month Names
• Determining What Is Selected in a ListBox
• Determining What Was Selected in a Multi-Select ListBox
• Auto Word Select in ComboBoxes
• How to Make a ComboBox a Dropdown Box
• How to Make a ComboBox Be Just a Drop Down ListBox
• Removing the Selection from a ComboBox
• Have UserForm ComboBox Drop Down When It Is Selected
• Problems with Dropdowns and Split Windows
• ComboBox.RowSource Returns "Type Mismatch"
• How to Assign Column Headings in ListBoxes
• Getting Column Headings in a ListBox
• Displaying a List Box with Multiple Columns
• Displaying Worksheet Names in a ListBox
• Printing Out What Is Selected in a ListBox
• Referring to ListBoxes on Worksheets
• Unselect in ListBox
• Initializing One ListBox Based on Another ListBox
• Putting Listbox Selection Into a TextBox or Cell
• Using a Horizontal Range for a List Box's Items
• Having a Macro Run When a Selection Is Made in a List Box
• Modifying an ActiveX Combobox on a Worksheet
• Internet Articles on ComboBoxes, EditBoxes, and ListBoxes

Other UserForm Objects

• Drawing Lines on UserForms
• How to Show a Chart, Map, WordArt, or Shape on a UserForm
• Putting Background Graphics on a UserForm
• Pasting Images to a UserForm Image Control
• Using a Calendar Control on a UserForm

Working with Worksheets

• Adding Worksheets
• Adding and Naming a New Sheet at the Same Time
• Adding a Worksheet as the Last Sheet in a Workbook
• How to Copy a Sheet and Make It the Last Sheet
• Sheet Copy Limit and the Cure
• How to Copy a Sheet to a New Workbook
• Worksheet.Copy Bug
• How to Delete Sheets
• Deleting Sheets Without Confirmation
• Getting the Exact Number of Worksheets in a Workbook
• How to Determine If a Sheet Exists in a Workbook
• How to Determine If a Worksheet Is Empty
• How to See If Worksheet Is Empty
• Clearing a Worksheet on Open
• How to Loop Through Your Sheets
• Sorting Sheets by Name
• Creating a List of Sheets in a Workbook
• Protecting and UnProtecting Worksheets
• Using Controls on a Worksheet
• Protecting All the Sheets in a Workbook
• A Simple Modify All Worksheets Example
• Inserting the Current Date in All Worksheets
• Making All Sheets Visible
• Preventing a User from Adding a Sheet
• Using a Worksheet's Code Name
• Changing a Worksheet's CodeName
• Checking If a Control Exists on a Worksheet

Working with Charts

• Loop Through All Charts
• Relocating Embedded Charts by Code
• Creating a Chart on a New Sheet
• Deleting All Embedded Charts on a Worksheet
• Making Charts Using Visual Basic Code
• Changing the Size of Embedded Charts
• Replicating Charts
• How to Export Charts to GIF Files
• Value of a Point on a Line
• An Add an Embedded Chart Example
• Changing a Chart's Size and Position
• Determining If a Series Is Selected in a Chart
• Changing the Title on an Embedded Chart
• Relocating a Chart
• Determining What a User Has Selected in a Chart
• Converting Chart Series References to Values
• Labeling the Points on a Line
• Putting Charts on UserForms

General Workbook Topics

• How to Open a Workbook
• Just Opened Workbook Not the Active File
• Determining If a Workbook Is Open
• How to Determine If a File Is Open
• Testing for File or Workbook Existence Before Opening
• Adding or Opening Workbooks
• Adding Workbooks
• Determining If a File Is Open in ReadOnly Mode
• Finding Workbook Links
• Changing the ReadOnly Status of a File
• How to Retrieve Names of Workbooks, Sheets, Etc.
• Saving a Workbook With Its Name Equal to the Current Date
• How to Not Save a Workbook When It Closes
• Closing All But the Active Workbook
• Returning the Full Path and Name of a Workbook
• Determining the Date and Time a File or Workbook Was Last Saved
• Open the Last Modified File in a Directory
• Counting Visible (Non-Hidden) Workbooks
• Extracting Values from Closed Workbooks
• Error 'The File Is Already Open...'

Selecting and Opening Workbooks

• Using the Built-in File Open Dialog
• Displaying the Built-in Dialogs
• Using the Excel File Open Dialog to Open Multiple Files
• Setting the Default GetOpenFilename Directory
• Finding the Last Modified File in a Directory
• ChDrive and Network Paths
• Using FileSearch Instead of the DIR Command
• Opening All the Files in a Directory
• Getting Values from a Closed Workbook
• Changing to a Floppy Drive
• Open a File Only If No One Else Has It Open

Copying, Moving, Renaming and Deleting

• Copying, Moving, and Renaming a File Without Opening It
• Using FileCopy to Copy Files or Workbooks
• How to Delete a File
• Getting Document and File Properties
• Deleting Files and Directories So That You Can Undelete Them
• Saving Files or Workbooks
• Eliminating the File Exists... Message When Using the SaveAs Method
• Saving a File in a New Directory With a New Name
• How to Save a Workbook in Excel 5 Format
• Make File Saving Mandatory
• Saving a Backup Copy of a File

Working with CSV Files and ASCII Files

• Displaying a Dialog to Have the User Select a CSV File
• Creating CSV Files
• How to Save as Text File Without Quotation Marks
• Save as CSV Using a Semicolon
• Reading a Text File Line by Line
• Issues With Reading CSV Files
• Writing Directly to an ASCII File
• Sheet/Range Extract to ASCII Files
• Importing Text Files
• CSV Files and Non-ASCII Characters
• Read Text File With Variable Length Records
• Importing Text File With Any Delimiter
• Saving the Active Sheet as a Comma Delimited File

Printing Examples

• A Fast Way to Set the Page Setup
• How to Speed Up Changing Print Settings
• How to Set the Print Area
• Determining the Print Area
• Enlarging a Print Area Range
• Add or Exclude an Area From Print_Area
• Updating the Header or Footer Before Printing
• Restricting Options in PrintPreview
• Memory Problems With Page Setup
• How to Fit the Printout to One Page
• Controlling Printing
• Printing Directly to a Printer
• How to Have the User Change the Active Printer
• How to Determine the Number of Pages That Will Print
• Getting the Number of Pages That Will Print
• Printing Using Range Names
• Adding Page Breaks to Your Code
• Determining PageBreak Locations
• Locating Page Breaks
• How to Find Next Automatic Page Break
• Removing Page Breaks
• Printing Each Row in a Selection Onto a Separate Page
• Printing From a Dialogsheet
• How to Printout a Sheet or an Entire Workbook
• Printing All the Files in a Directory
• Printing Embedded Charts
• Case of the Disappearing PageBreak Constant
• Changing the Paper Type on Each Sheet in a Workbook
• File Path in Footer
• Hiding the Windows Print Dialog

Directory Examples

• Displaying the Windows 95 Folder Dialog to Select a Directory
• Specifying a Starting Directory
• Getting a Directory Using the File Open Dialog
• How to Have the User Select a Directory
• Setting the Directory for UnMapped Network Drives
• Getting a List of Subdirectories
• Listing Sub Directories in a Directory
• Determining If a Directory Exists
• Listing Files in a Directory And/Or Its Subdirectories
• Counting the Number of Files in a Directory
• How to Obtain the User's Temp Directory
• Getting the Windows Directory
• Getting File Information From a Directory
• Creating a New Directory
• Creating a Multi-Level Directory
• List of Available Drives
• Getting the Amount of Free Disk Space on a Drive

Progress Messages and Splash Screens

• Creating a Splash Screen While Your Code Runs
• Displaying a Status Bar Message
• Rather Cool Non Modal Progress Dialog
• Modeless Userforms in Excel 2000
• Resetting the Status Bar
• Display Status Messages in a Modeless UserForm
• Modeless Dialogs
• Displaying a MsgBox for X Seconds

Function Examples

• A Function That Uses Multiple Ranges as Input
• An Example Function
• Determining Which Cell, Worksheet, and Workbook Is Calling a Function
• Finding the Maximum Value in a Column
• Forcing a Function to Recalculate When a Change Is Made
• Getting the Maximum Value in a Range
• Tricks on Using Find
• VLookup Example
• User Defined Functions
• Using Worksheet Functions in Visual Basic Macros
• Using the Worksheet Functions in Your Code
• Using Match to Return a Row or Column Number
• User Defined Functions and the Function Wizard
• Using Find in Visual Basic Code
• Using Find in Your Macros
• Using the Find Command to Find a Particular Cell
• Using VLookUp in Your Code
• Using Application.Caller to Determine What Called a Function
• Why Functions Can't Change Cells

Window Examples

• Determining the Visible Range in a Window
• How to Make a Range the Visible Range in a Window
• Automatically Displaying a Sheet in Full Screen Mode
• Disabling Window Minimization
• Displaying the Full Screen Without the Full Screen Toolbar
• Determining the Window State
• Finding Out Which Cell Is in the Upper Left Corner
• Getting a Window's Handle and Other Information
• Getting the Monitor's Screen Resolution
• Getting the Screen Resolution
• Hiding a Worksheet While a Dialog or UserForm Is Displayed
• Hiding and Showing Windows
• How to Change the Excel Window Caption
• How to Keep the Workbook Window Maximized
• How to Maximize the Window
• Positioning the Excel Window
• Setting All Worksheets to the Same Scroll Position
• Sizing a Worksheet to Fit the Screen
• Synchronizing Windows on Different Sheets
• Unhiding Hidden Workbooks

Filtered Data Examples

• AutoFilter's Range
• Determining Filter Settings
• How to Select the Data in a Filtered List
• How to Turn AutoFilter Off and On
• Determining If AutoFilter Is Turned On
• Determining the AutoFilter's Settings
• Working With Just the Filtered Cells on a Sheet

Pivot Table Examples

• Expanding Pivot Table Ranges
• Clearing Incorrect Field Names in PivotTable Field Dialog Box
• Pivot Table Events

Date and Time Examples

• Converting the Date to a Day's Name
• Converting Now() to Hours, Minutes, Day, Month and Year
• Getting a Date Input From a User
• How to Find a Specified Time in a Specific Range
• How to Find a Date in a Range
• Using a Macro to Insert Current Time
• Having Excel Wait for a Few Seconds
• Application.Wait
• Date Comparisons
• Using Code to Create a Calendar in a Worksheet
• Getting the End of a Month
• Inserting the Date on Every Worksheet and Footer
• Using Milliseconds When Excel Waits
• Writing the Date and Time Out to a Cell
• Measuring Time Change
• Automatically Entering the Date Into an Edit Box
• Days Left Counter
• Select Case Using Dates
• Validating Date Entries

Shortcut Key Examples

• Individual Disable Shortcut Keys
• Redefining the Plus and Minus Keys
• Disabling Almost All of the Shortcut Keys
• Disabling Shortcut Menu Commands
• Making Shortcut Keys Sheet Specific

Toolbar Examples

• Using Attached Toolbars
• Resetting the Macros on a Custom Toolbar
• Using a Macro to Create a Toolbar
• Using FaceIDs to Specify a Toolbar Button Face
• Putting Custom Button Faces on Toolbar Buttons
• Hiding and Restoring the Toolbars and Menus
• How to Prevent Your Custom Toolbar Buttons From Appearing Faded
• Adding Tool Tips to Buttons

CommandBar and Menu Examples

• Using Excel's Built-in CommandBars
• CommandBar.Add Yields Err 91 on Workbook_Open
• Adding a Menu Item to a Menu
• Adding a Menu and Sub Menus to the Worksheet Menu
• How to Add a New Menu Bar Like the Worksheet Menu Bar
• Button Like Control on a Menu
• Hiding the Worksheet Menu
• Putting a DropDown on a CommandBar
• Creating a Menu That Appears Only When a Particular Workbook Is Active
• Adding a Menu and Menu Items to the Worksheet Menu
• Adding a New Menu to the Worksheet Menu
• Disable SaveAs Menu
• Resetting the Menus
• Protecting Commandbars
• How to Add a Menu Item Separator Bar
• Determining Which Button Was Clicked on a Toolbar
• CommandBars and Control Numbers
• How to Add a Short Cut Menu
• TextBoxes on CommandBars
• Listing the Shortcut Menus
• Menu Code Available on the Internet
• Internet Articles on How to Change the Menus

Button and Other Control Examples

• Assigning a Macro to a Button
• Working With Command Buttons
• Problems With Buttons and Controls
• Hiding Controls Placed on Worksheets
• How to Remove Buttons From a Sheet
• Hiding or Showing Combo Boxes via Code
• Creating Combo Boxes With Code
• Preventing Typing in a ComboBox
• How to Have a Worksheet ComboBox Drop Down
• Self Modifying List Box Example

Pop-Ups

• Disabling the Cells Shortcut Menu
• Replacing the Cell Pop-Up
• Disabling the Right Click Pop-Up
• Disabling the Tool List Pop-Up
• How to Customize the Popup Menus
• Creating and Assigning a Custom Pop-Up
• Disabling the Worksheet Tab and Navigation Pop-Up

Debugging and Handling Errors

• Debugging Tricks
• Break on Unhandled Errors
• Error Trapping
• Avoiding Excel/VBA Crashes
• Modifying Code and Repeating Steps While Debugging
• Error Handling Different in Functions
• What to Do If You Get Strange Problems With Perfectly Good Code
• Observing Excel While Debugging in Visual Basic
• Detecting Error Values in Cells
• Out of Memory Error Solutions
• Excel Crashes When Using a Range
• Stack Overflow / Out of Memory Problems
• Keeping an Error Handler in Effect After an Error Occurs
• Excel Crashes When a UserForm Is Displayed
• Error Handling and Getting the Error Line
• ErrObject

Excel 5/7 Dialogsheets

• How to Create and Display Dialogsheets
• Selecting a Range Using an Excel 5/7 Dialog Sheet
• Changing the Name of Your Dialogsheet Objects
• Centering a Dialog in Excel 5/7
• Setting the Tab Order in a DialogSheet
• Displaying Dialogsheets

Controlling User Interruptions

• Capturing When Esc or Ctrl-Break Is Pressed
• Keeping Your Code From Being Stopped by the Esc or Ctrl-Break
• Determining Which Key Was Pressed

Event Handling

• Auto_Open and Workbook_Open Macros
• Preventing an Auto_Open or Workbook_Open Macro From Running
• Having a Dialog Appear When a Workbook Is First Opened
• Running a Macro Whenever a Workbook Is Closed
• Order of Close Events
• Intercepting the Excel and Workbook Close Events
• Disabling Events From Running
• Running a Macro Every Minute
• OnTime Method
• How to Make a Macro Run Every Two Minutes
• How to Cancel an OnTime Macro
• Detecting When a Cell Is Changed
• Macro Execution Linked to Cell Entry
• How to Run a Macro When the User Changes the Selection
• How to Run a Macro When a Sheet Is Activated
• Excel Events That Are Triggered When a Cell Changes
• Using the Worksheet Change Event
• Validating User Entries Using OnEntry
• Auto Capitalizing
• Using OnEntry to Force Entries to Be Uppercase
• Running a Macro When the User Double Clicks
• Preventing a User From Closing a File
• Preventing a User From Closing Any File
• Using Application.Caller and OnEntry Macros
• Stopping Event Looping
• Capturing When the User Changes the Selected Cell
• Determining When a Worksheet Is Selected or a Workbook Activated
• Canceling a Close Event

HTML Topics

• Converting Data to HTML
• Opening a Hyperlink
• Opening an HTML Page From Excel
• Save as HTML
• How to Create HTML From a Macro
• Deleting HyperLinks
• How to Invoke a Hyperlink
• Getting a Cell's Hyperlink
• Speed Up the "Save As HTML" Process
• Getting Stock Prices From a Web HTTP Query

Working with Other Applications

• Using Excel to Send E-Mails
• Sending E-Mail From Outlook Express
• Sending E-Mail From Outlook
• Sending E-Mail From Excel
• How to Send an E-Mail From a SMTP System
• Using Outlook to Send Mail
• E-Mailing a File From Outlook
• Launching Another Windows Program or Application
• Running a Shortcut From a Macro
• Open Windows Explorer
• Getting Excel to Pause While a Shell Process Is Running
• Activating a Running Application
• Determining If Another Application Is Running
• Starting Word From Excel
• Opening a MS Word Document From Excel
• Running Word Macros From Excel
• Opening a PowerPoint Presentation
• Displaying a DOS Window
• Getting Data From Access
• How to Exchange Data Between Access and Excel
• SQL Query Strings
• Excel GetObject to Open Word
• Using Barcodes in Excel

Neat Things to Know

• Using SendKeys in Your Macros
• Hiding the Active Menu and Using Full Screen
• Hiding Screen Update Activity
• Stopping Alert Messages / Display Alert Warning
• Speeding Up Your Procedures and Controlling Calculation
• Speeding Up Your Procedures
• Macros Run Really Slow
• Determining How Long Your Code Took to Run
• A Solution to Excel Running Slow
• How to Hide Excel Itself
• Opening Without a Blank Workbook and No Splash Screen
• Closing Excel via Visual Basic With Application.Quit
• Playing WAV Files
• Running Macros That Are Located in a Different Workbook
• Writing Text to a Shape or Text Box
• How to Prevent a Macro From Showing in the Macro List
• Using SendKeys to Force a Recalculation
• Bypassing the Warning About Macros
• Hiding the Cell Pointer
• Turning the Caps Lock Key Off or On
• Modifying the Windows Registry
• Getting Values From the Registry
• Removing an Outline
• Turning Num Lock Off or On
• Turning Scroll Lock Off or On
• Disabling the Delete Key
• Writing to the Serial Port
• COM Ports
• Capturing Win 95 Network Login User Name
• Convert to PDF File via VBA
• Using the Mouse Wheel Anywhere
• How to Display HTML Help Files
• Turn Off Asterisk as Wildcard
• Preventing VBA Help From Resizing the Editor
• VBA Screen Capture Routines

Interesting Macro Examples

• An Example of a Rounding Macro
• Finding Entries That Are Not in a List
• Deleting Rows Based on Entries in a Column
• How to Convert Formulas to Absolute References
• A Database Modification Example
• Generating Unique Sequential Numbers for Invoices
• Generating Random Numbers
• Deleting Leading Tick Marks From a Selection
• Counting Unique Values in a Range

Return to Macros Examples main page