View Cart

Quantity
Discounts

Money back
guarantee

Macros Examples for Microsoft Excel

Order online from our secure service - Get delivery in minutes!
Order Risk Free - Money back guarantee if not satisfied

   US $19.95  
Buy individually or as part of the Productivity Suite and save!

Return to Macros Examples main page

The following provides you detail on each chapter in the book:

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 Variables Module Variables 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
• How To Determine If A Selection Has Non
• 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
• Determining The Last Cell In A Column
• Finding The Last Entry In A Column
• Finding The Last Non
• 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

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 Is Empty
• 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

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 Used Range Property In Your Code
• Resetting the Used Range
• 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 Appears 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 An 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 An 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 Forms
• 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 Module
• 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 Userforms
• 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 Ref Edit 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
• 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 Item
• Having A Macro Run When A Selection Is Made In A List Box
• ComboBox.RowSource Returns "Type Mismatch"
• 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 Shape Etc 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
• Extracting Values From Closed Workbooks
• Error 'The File Is Already Open...'

SELECTING AND OPENING WORKBOOKS

• Using The Built
• Displaying The Built
• 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 Quotations Marks
• Save As CSV Using A Semi
• 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
• 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 PageBreaks 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
• 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 Worksheets 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 A 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
• 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
• Disabling The Right Click Pop
• Disabling The Tool List Pop
• Replacing The Cell Pop
• How To Customize The Popup Menus
• Creating and assigning a custom Pop
• Disabling The Worksheet Tab And Navigation Pop

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 Handling 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
• Keeping Your Code From Being Stopped By The Esc Or Ctrl
• 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 A 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 Window 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