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

by González, Juan Pablo; Meister, Cindy; Ozgur, Suat; Dilworth, Bill; Troy, Anne; Brandt, T J

• ISBN13: 9781932802061
• ISBN10: 1932802061
• Format: Paperback
• Copyright: 2006-01-01
• Publisher: Holy Macro! Books

Summary

For any Microsoft Office user familiar with Word, Excel, PowerPoint, Access, or Outlook, this book provides tutorials on the tools and tricks that automate these programs for increased productivity. Each lesson includes instructions and a downloadable file on which to test the VBA macros and procedures provided. Tasks covered include how to run several mail merges at once, removing duplicates from data, and saving email attachments.

Author Biography

Juan Pablo Gonz�1lez is a professional VBA developer and a Microsoft MVP in Excel. He lives in Carbondale, Illinois. Cindy Meister is a Microsoft MVP in Word. Suat Ozgur is a Microsoft Office expert. Bill Dilworth is a Microsoft MVP in PowerPoint.

Table of Contents

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