|
Macros Examples for Microsoft® Excel®
Order online from our secure service - Get
delivery in minutes!
Order Risk Free - Money back guarantee if not
satisfied
$29.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
- The Menu Editor And Excel 97/2000
- Determining the Excel Version
- Excel 2000 VBA vs. Excel 97 VBA
- Excel Runtime 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
- Office 97 VBA Programming Book Is Available Online
- 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
- Installing The Excel 97 Security Patch
- Translating 123 Macros
- Converting Lotus 1-2-3 Macros
- The Equivalent Of A Lotus 1-2-3 Macro Pause
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 Excel 97/2000 For 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
|
