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

Foreword......Page 11
Cover......Page 1
Table of Contents......Page 5
Juan Pablo González......Page 13
Suat Ozgur......Page 14
Nico Altink......Page 15
Contributors......Page 16
Take Advantage of UserForms......Page 17
Enjoy Security......Page 18
Understanding Code Comments......Page 19
Identifying Procedure Components......Page 20
Version Compatibility......Page 21
Introducing the Visual Basic Editor......Page 23
Accessing Visual Basic Editor (VBE)......Page 24
Touring the VBE Toolbar......Page 25
 Setting VBE Options......Page 27
Using Project Explorer......Page 28
 Understanding VBA Project......Page 29
Working with Modules......Page 30
Using the Main Code Window......Page 32
 Protecting Your Projects......Page 34
Printing All Files......Page 35
Saving a Workbook as Today's Date......Page 37
Highlighting Duplicates Within a Range......Page 39
Sorting Worksheets......Page 41
Generating a Unique List......Page 43
 Using AutoFilter on a Protected Sheet......Page 46
Deleting Rows Based on Criteria......Page 47
 Checking Whether or Not a File Exists......Page 51
Removing Hyperlinks......Page 52
Applying SUM / COUNT by Color......Page 53
Using More Than Three Conditional Formats......Page 56
Providing a Calendar to Choose Dates for Input......Page 58
Restricting Text Box Entry to Numbers......Page 61
Running a Macro When a Cell Changes......Page 63
Forcing the Use of a Custom Print Procedure......Page 65
Restricting the User to a Portion of the Worksheet......Page 66
Copying a Workbook with Macros Removed......Page 68
 Inserting Empty Rows in a Range......Page 70
Creating a Custom Toolbar......Page 72
 Creating a Table of Contents of a Workbook......Page 76
Changing the Case of Text......Page 78
Creating a Photo Album......Page 80
 Deleting the Empty Rows in a Range......Page 83
Creating a List of Files That Reside in a Directory......Page 85
 Forcing the User to Enable Macros......Page 89
Finding and Replacing a String in All Open Workbooks......Page 91
Converting Data to a Tabular Format......Page 92
AutoNumbering Invoices and Other Workbooks......Page 95
Comparing Columns Using Various Criteria......Page 96
Deleting the Contents of Unlocked Cells......Page 99
Hiding All Standard Toolbars Except Your Own......Page 101
Creating a PPT Presentation from a Pivot Chart......Page 104
 Saving a Backup Copy of a Workbook......Page 109
 Importing Your Contacts from Outlook......Page 112
 E-mailing from Excel with Outlook......Page 115
 Printing a UserForm......Page 118
 Importing and Formatting a Text File......Page 121
 Extracting Numbers from a Text String......Page 125
Finding and Deleting Erroneously Named Ranges......Page 127
 Logging Actions When a Cell Changes......Page 129
Synchronizing Page Fields of Pivot Tables......Page 132
Applying Your Favorite Bullet/Number Format......Page 135
 Finding and Replacing in Multiple Documents......Page 138
Highlighting a Selection......Page 142
Highlighting a Selection in Word 2002/XP......Page 143
 Removing All Highlighting......Page 145
 Inserting AutoText with No Formatting......Page 146
Updating All Fields......Page 147
 Setting Hyperlinks on Index Entries......Page 148
Displaying a Number in Millions as Text......Page 154
Copying Nested Field Codes as Text......Page 157
Reverse Numbering......Page 160
Tables: Changing the Tab Direction......Page 162
Tables: Suppressing New Rows When Tabbing......Page 164
Tables: Formatting Numbers in a Selection......Page 165
Tables: Copying Formulas......Page 167
 Using Calendar Wizard......Page 173
Changing the Font......Page 179
Inserting a Picture with Caption......Page 180
Leaving off the Caption......Page 184
Wrapping Text Around the Frame......Page 185
Associating a Picture with a Page......Page 186
Forms: Suppressing New Paragraphs in Form Fields......Page 191
Forms: Formatting Text Input in Form Fields......Page 194
Changing Other Types of Formatting......Page 198
 Forms: Inserting a New Table Row......Page 199
Forms: Deleting a Table Row......Page 205
Forms: Placing a Picture in a Protected Form......Page 209
Mail Merge: Using a Relative Path for Data Source......Page 211
Mail Merge: Displaying the Mail Merge Interface......Page 214
Mail Merge: Creating a User-Friendly List of Fields......Page 216
Mail Merge: Making Placecards Using WordArt......Page 219
Mail Merge: Creating a One-to-Many List......Page 221
Mail Merge: Merging with a Chart......Page 231
Transferring a Selection to a New Document......Page 241
 Splitting a Document into Multiple Files......Page 246
 Creating a Folder Tree Menu......Page 252
Changing Custom Dictionaries On-the-Fly......Page 261
Formatting Spelling Errors for Printing......Page 265
Entering Data Easily Using a Custom Dialog Box......Page 270
Creating a Bookmark from a Selection......Page 276
Making Bookmarks Visible......Page 280
Generating the Document Using VBA......Page 281
Macros in Files Opened by Code......Page 283
Creating Control Buttons......Page 285
Saving E-mail Attachments in a Specified Folder......Page 287
Creating a Contacts Database......Page 290
Sending a Web Page as the Body of an E-mail Message......Page 295
Sending a Message Individually to Multiple Recipients......Page 298
Sending Daily Attachments to Certain Recipients......Page 303
Creating Reminders Automatically......Page 306
Creating Task Items Automatically in Outlook......Page 310
Special: Outlook Security......Page 316
Auto Replying to Selected E-mail Messages......Page 317
Remote Control with Outlook E-mail Message......Page 318
Inserting a Predefined Number of Slides......Page 321
Manipulating AutoShapes......Page 322
Grabbing All Text......Page 324
Moving Shapes and Graphics During Presentation......Page 326
Making a Random Jump to Another Slide......Page 329
 Random Madness......Page 331
Sending Word Outline to Notes Section of PowerPoint......Page 332
Wrapping Text to the Next Slide......Page 335
Saving the Show Point......Page 337
Personalizing a Presentation......Page 338
Creating a New Presentation......Page 340
Splitting Names......Page 343
Designing Consistent Forms......Page 347
Triggering a New Form Based on a Subform Selection......Page 350
Selecting and Filtering with Cascading Combo Boxes......Page 353
E-mailing a Selection......Page 358
Making a Rolodex-type Selection List Box......Page 362
Validating Data......Page 366
 Moving Rows Between List Boxes......Page 369
Moving Rows in List Boxes......Page 371
Creating a Dynamic Crosstab Report......Page 373
Generating Periodic Reports......Page 375
Creating Controlled Numbers......Page 377
Making a Wizard with Tabbed Control......Page 379
Transferring Charts From Excel to PowerPoint......Page 383
Saving Word Form Data to an Excel Spreadsheet......Page 384
Filling a Word Combo Box with Data from Excel......Page 387
Transferring Data from E-mail Attachments to Excel......Page 394
Creating Word Labels from an Excel Recipient List......Page 398
 Creating Custom Mail Merge Using Data in Excel Worksheet......Page 405
Using Calendar Control for Office Applications......Page 407
Opening and Using the Visual Basic Editor......Page 419
Locating the Code Object......Page 420
Inserting a Module......Page 422
Inserting a UserForm......Page 423
Opening Worksheet Objects (Excel)......Page 426
Opening ThisDocument Objects (Word)......Page 428
Opening ThisOutlookSession Objects (Outlook)......Page 429
Opening Slide Objects (Powerpoint)......Page 430
Access Objects......Page 431
BookmarkTitle:......Page 432
Running a Macro Manually......Page 433
Running a Macro from a Toolbar Button......Page 434
Running a Macro Using Shortcut Keys......Page 435
Index......Page 437
Back Cover......Page 451