Excel Macros For Dummies. Dick Kusleika

Excel Macros For Dummies

Type or paste your code in the Workbook BeforeClose event Code.FIGURE 4-6: Type or paste your code in the Workbook Open event Code pane.FIGURE 4-7: You want your workbook to automatically open to the sheet called St...FIGURE 4-8: Type or paste your code in the Workbook Open event Code pane.FIGURE 4-9: The macro opens the Open dialog box.FIGURE 4-10: You can avoid this annoying message box when opening a workbook th...FIGURE 4-11: You can prevent your workbook from closing until a specific cell i...FIGURE 4-12: Type or paste your code in the Workbook_BeforeClose event Code pan...

5 Chapter 5FIGURE 5-1: Omit the Application.DisplayAlerts = False line in the macro to ens...FIGURE 5-2: Without a macro, you’re stuck using Excel’s Unhide dialog box to un...FIGURE 5-3: It’s often useful to have your worksheets sorted in alphabetical or...FIGURE 5-4: You can right-click any worksheet to choose a tab color for the she...FIGURE 5-5: The Protect Sheet command is disabled if you try to protect more th...FIGURE 5-6: The Unprotect Sheet command is disabled if you try to unprotect mor...FIGURE 5-7: A table of contents can help you more easily navigate your workbook...FIGURE 5-8: Type or paste your code into the Worksheet_BeforeDoubleClick event ...FIGURE 5-9: A highlighted row and column makes it easy to track data horizontal...

6 Chapter 6FIGURE 6-1: Click the Define Name command to name a chosen range.FIGURE 6-2: Give your range a name.FIGURE 6-3: The Name Manager dialog box lists all the applied named ranges.FIGURE 6-4: Type or paste your code in the Workbook Open event Code pane.FIGURE 6-5: The Go To Special dialog box.FIGURE 6-6: Choosing Formulas in the Go To Special dialog box tells Excel to se...FIGURE 6-7: You can use a macro to tell Excel to dynamically find the first ava...

7 Chapter 7FIGURE 7-1: Imported numbers are sometimes formatted as text.FIGURE 7-2: Click the Text to Columns command.FIGURE 7-3: Clicking Finish in the Text to Columns dialog box corrects incorrec...FIGURE 7-4: This macro dynamically finds and highlights the duplicate values in...FIGURE 7-5: This macro ensures that only those rows that contain duplicate valu...FIGURE 7-6: The standard AutoFilter functionality adds drop-down arrows to all ...FIGURE 7-7: With a little VBA, you can choose to hide certain AutoFilter drop-d...FIGURE 7-8: This macro lists all filtered columns in the status bar.FIGURE 7-9: Type or paste your code in the Worksheet_Calculate event Code pane.

8 Chapter 8FIGURE 8-1: A PivotTable inventory summary.FIGURE 8-2: Excel shows the enumeration choices.FIGURE 8-3: Type or paste your code in the Worksheet_BeforeDoubleClick event Co...FIGURE 8-4: Enter or paste your code in the Workbook_BeforeClose event Code pan...FIGURE 8-5: The Selection pane allows you to see all of your chart objects' nam...

9 Chapter 10FIGURE 10-1: Show legacy wizards in the Ribbon.FIGURE 10-2: Choose the source database that contains the data you want importe...FIGURE 10-3: Select the Access object you want to import.FIGURE 10-4: Choosing how and where to view your Access data.FIGURE 10-5: Data imported from Access.FIGURE 10-6: As long as a connection to your database is available, you can upd...FIGURE 10-7: Select the SQL command type and enter your SQL statement.FIGURE 10-8: Designate a cell that will trap the criteria selection.FIGURE 10-9: You now have an easy-to-use mechanism to pull external data for a ...FIGURE 10-10: Select the latest version of the Microsoft ActiveX Data Objects L...

10 Chapter 11FIGURE 11-1: A single apostrophe in front of any line turns that line into a co...FIGURE 11-2: The Edit toolbar allows you to select entire blocks of code, and t...FIGURE 11-3: Holding down the Ctrl key while dragging code creates a copy of th...FIGURE 11-4: Pressing Shift+F2 with your cursor on a function or variable name ...FIGURE 11-5: Use the Procedure View button to limit scrolling to just the activ...FIGURE 11-6: Press F8 key to step through each line of your macro at your own p...FIGURE 11-7: You can click and drag the yellow arrow while stepping through you...FIGURE 11-8: A breakpoint is marked by a dot in the left margin along with shad...FIGURE 11-9: Showing the beginning and ending characters in a variable tooltip.FIGURE 11-10: Leaving a line of code unfinished, even for a second, results in ...FIGURE 11-11: Uncheck the Auto Syntax Check option to prevent warning messages ...