Microsoft Office Tutorials and References
In Depth Information
In this lesson, you attach a macro to a Forms button that will toggle certain columns as being visible or
hidden. Along the way, you learn a few tricks about faster methods for entering data into multiple cells.
For this lesson, you place a Forms button on a worksheet that contains a hypothetical table of monthly
income activity for a department store’s clothing items. A macro will be attached to the button that,
when clicked, will toggle columns or rows as being hidden or visible, depending on how you want to
see the data. Upon each click of the button, the cycle of views will be to see the entire table’s detail,
see totals only by clothing item, or see totals only by month. This lesson also includes tips on fast data
entry by using the ill handle and shortcut keys. To get the sample database files you can download
Open Excel and open a new workbook.
On your active worksheet, list the months of the year in range
A6:A17. You can do this quickly by entering January in cell A6, then
selecting A6, and pointing your mouse over the ill handle, which is
the small black square in the lower-right corner of the selected cell.
You know your mouse is hovering over the ill handle when the cursor
changes to a crosshairs, as indicated in Figure 13-17. Press your left
mouse button onto the ill handle, and drag your mouse down to cell
A17 as indicated in Figure 13-18. Release the mouse button, and the
12 months of the year will be filled into range A6:A17 as shown in Figure 13-19.
Enter some clothing items into B5:F5.
Enter sample numbers in range B6:F17. There is nothing special about the numbers; they are
just for demonstration purposes. To enter the numbers quickly as shown in Figure 13-20, do
➤ Select range B6:F17.
➤ Type the formula
= INT(RAND()*1000) .
➤ Press the Ctrl+Enter keys.
➤ Press the Ctrl+C keys to copy
➤ Right-click somewhere in the
range B6:F17, and select
Paste Special ➪ Values ➪ OK.
➤ Press the Esc key to exit Copy