Add-ins.com home page
Questions? E-mail us or call us 302-584-1771, 8AM to 10PM U.S. East Coast Time 7 days a week
     

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

Add to shopping cart    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


Copyright 2024 Add-ins.com LLC, all rights reserved.  Spreadsheet Assistant is a registered trademark of Add-ins.com LLC.