Office VBA Macros You Can Use Today: Over 100 Amazing Ways to Automate Word, Excel, PowerPoint, Outlook, and Access Book

	Foreword	a
	About the Authors and Contributors	c
	Juan Pablo Gonzalez	c
	Cindy Meister	d
	Suat Ozgur	d
	Bill Dilworth	e
	Nico Altink	e
	Contributors	f
	Introduction	1
	What is VBA?	1
	Save Time	1
	Take Advantage of UserForms	1
	Develop User-defined Functions	2
	Enjoy Security	2
	How to Use This Book	3
	Using the Procedures	3
	Understanding Code Comments	3
	Inserting Paragraph Returns in Code	4
	Using Menu Commands	4
	Identifying Procedure Components	4
	Using Sample Files	5
	Backup Your Files!	5
	Version Compatibility	5
	Introducing the Visual Basic Editor	7
	Accessing Visual Basic Editor (VBE)	8
	Touring the VBE Toolbar	9
	Setting VBE Options	11
	Using Project Explorer	12
	Understanding VBA Project	13
	Working with Modules	14
	Using the Main Code Window	16
	Protecting Your Projects	18
	Excel Procedures	19
	Printing All Files	19
	Saving a Workbook as Today's Date	21
	Highlighting Duplicates Within a Range	23
	Sorting Worksheets	25
	Generating a Unique List	27
	Using AutoFilter on a Protected Sheet	30
	Deleting Rows Based on Criteria	31
	Checking Whether or Not a File Exists	35
	Removing Hyperlinks	36
	Applying SUM / COUNT by Color	37
	Using More Than Three Conditional Formats	40
	Providing a Calendar to Choose Dates for Input	42
	Restricting Text Box Entry to Numbers	45
	Running a Macro When a Cell Changes	47
	Forcing the Use of a Custom Print Procedure	49
	Restricting the User to a Portion of the Worksheet	50
	Copying a Workbook with Macros Removed	52
	Inserting Empty Rows in a Range	54
	Creating a Custom Toolbar	56
	Creating a Table of Contents of a Workbook	60
	Changing the Case of Text	62
	Creating a Photo Album	64
	Deleting the Empty Rows in a Range	67
	Creating a List of Files That Reside in a Directory	69
	Forcing the User to Enable Macros	73
	Finding and Replacing a String in All Open Workbooks	75
	Converting Data to a Tabular Format	76
	AutoNumbering Invoices and Other Workbooks	79
	Comparing Columns Using Various Criteria	80
	Deleting the Contents of Unlocked Cells	83
	Hiding All Standard Toolbars Except Your Own	85
	Creating a PPT Presentation from a Pivot Chart	88
	Saving a Backup Copy of a Workbook	93
	Importing Your Contacts from Outlook	96
	E-mailing from Excel with Outlook	99
	Printing a UserForm	102
	Importing and Formatting a Text File	105
	Extracting Numbers from a Text String	109
	Finding and Deleting Erroneously Named Ranges	111
	Logging Actions When a Cell Changes	113
	Synchronizing Page Fields of Pivot Tables	116
	Word Procedures	119
	Applying Your Favorite Bullet/Number Format	119
	Finding and Replacing in Multiple Documents	122
	Highlighting a Selection	126
	Highlighting a Selection in Word 2002/XP	127
	Removing All Highlighting	129
	Inserting AutoText with No Formatting	130
	Updating All Fields	131
	Setting Hyperlinks on Index Entries	132
	Displaying a Number in Millions as Text	138
	Copying Nested Field Codes as Text	141
	Converting AutoNumbered Text into Normal Text	144
	Reverse Numbering	144
	Tables: Changing the Tab Direction	146
	Tables: Suppressing New Rows When Tabbing	148
	Tables: Formatting Numbers in a Selection	149
	Tables: Copying Formulas	151
	Using Calendar Wizard	157
	Formatting Your Calendar	163
	Inserting a Picture with Caption	164
	Making Changes	168
	Associating a Picture with a Page	170
	Forms: Suppressing New Paragraphs in Form Fields	175
	Forms: Formatting Text Input in Form Fields	178
	Changing Other Types of Formatting	182
	Forms: Inserting a New Table Row	183
	Forms: Deleting a Table Row	189
	Forms: Placing a Picture in a Protected Form	193
	Mail Merge: Using a Relative Path for Data Source	195
	Mail Merge: Displaying the Mail Merge Interface	198
	Mail Merge: Creating a User-Friendly List of Fields	200
	Mail Merge: Making Placecards Using WordArt	203
	Mail Merge: Creating a One-to-Many List	205
	Mail Merge: Merging with a Chart	215
	Transferring a Selection to a New Document	225
	Splitting a Document into Multiple Files	230
	Creating a Folder Tree Menu	236
	Changing Custom Dictionaries On-the-Fly	245
	Formatting Spelling Errors for Printing	249
	Entering Data Easily Using a Custom Dialog Box	254
	Creating a Bookmark from a Selection	260
	Making Bookmarks Visible	264
	Forcing the User to Enable Macros	265
	Generating the Document Using VBA	265
	Using Forms Protection	267
	Macros in Files Opened by Code	267
	Outlook Procedures	269
	Creating Control Buttons	269
	Saving E-mail Attachments in a Specified Folder	271
	Creating a Contacts Database	274
	Sending a Web Page as the Body of an E-mail Message	279
	Sending a Message Individually to Multiple Recipients	282
	Sending Daily Attachments to Certain Recipients	287
	Creating Reminders Automatically	290
	Creating Task Items Automatically in Outlook	294
	Special: Outlook Security	300
	Auto Replying to Selected E-mail Messages	301
	Remote Control with Outlook E-mail Message	302
	PowerPoint Procedures	305
	Inserting a Predefined Number of Slides	305
	Manipulating AutoShapes	306
	Grabbing All Text	308
	Moving Shapes and Graphics During Presentation	310
	Making a Random Jump to Another Slide	313
	Random Madness	315
	Sending Word Outline to Notes Section of PowerPoint	316
	Wrapping Text to the Next Slide	319
	Saving the Show Point	321
	Personalizing a Presentation	322
	Creating a New Presentation	324
	Access Procedures	327
	Splitting Names	327
	Designing Consistent Forms	331
	Triggering a New Form Based on a Subform Selection	334
	Selecting and Filtering with Cascading Combo Boxes	337
	E-mailing a Selection	342
	Making a Rolodex-type Selection Listbox	346
	Validating Data	350
	Moving Rows Between Listboxes	353
	Moving Rows in Listboxes	355
	Creating a Dynamic Crosstab Report	357
	Generating Periodic Reports	359
	Creating Controlled Numbers	361
	Making a Wizard with Tabbed Control	363
	Combined Procedures	367
	Transferring Charts From Excel to PowerPoint	367
	Saving Word Form Data to an Excel Spreadsheet	368
	Filling a Word Combo Box with Data from Excel	371
	Transferring Data from E-mail Attachments to Excel	378
	Creating Word Labels from an Excel Recipient List	382
	Creating Custom Mail Merge Using Data in Excel Worksheet	389
	Using Calendar Control for Office Applications	391
Appendix A403
	Opening and Using the Visual Basic Editor	403
	Locating the Code Object	404
	Inserting a Module	406
	Inserting a UserForm	407
	Opening Worksheet Objects (Excel)	410
	Opening ThisWorkbook Object (Excel)	412
	Opening ThisDocument Objects (Word)	412
	Opening ThisOutlookSession Objects (Outlook)	413
	Opening Slide Objects (Powerpoint)	414
	Access Objects	415
Appendix B417
	Running a macro	417
	Running a Macro Automatically	417
	Running a Macro Manually	417
	Running a Macro from a Toolbar Button	418
	Running a Macro Using Shortcut Keys	419
	Index	421