| 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 |
loading...