XL: Sample Macro to Set Print Titles in Active Worksheet

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, versions 7.0, 7.0a

SUMMARY

In Microsoft Excel, if you want to set print titles for a worksheet, you must use the Page Setup dialog box to enter range references. The steps and macro in the "More Information" section of this article allow you to use a custom dialog box to set these page attributes.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

To create the custom dialog box

1. In your Personal Macro workbook, create a new Dialog sheet.

   NOTE: To view your Personal Macro Workbook, click Unhide on the Window menu, select the Personal.xls file, and click OK.

2. Rename the dialog sheet "titles" (without the quotation marks).

3. In the dialog sheet, create two edit boxes, and position the boxes one above the other. (Leave room for a label above each box.)

4. Give the edit boxes the defined names "rowrange" and "colrange" (without the quotation marks).

5. Select the "rowrange" edit box, and click Object on the Format menu.

6. Select the Control tab. Under Edit Validation, select Reference. Click OK.

7. Repeat steps 5 and 6 for the "colrange" edit box.

8. Draw a label above each edit box. For the top label, type "Rows to Repeat at Top" (without the quotation marks), and for the bottom label, type "Columns to Repeat at Left" (without the quotation marks).

To create the macro

In your Personal Macro workbook, insert a new module sheet, and enter the following code in the module sheet:

   Sub Set_Print_Titles()

       With DialogSheets("titles")

       'If the user presses Cancel in the Dialog box, then the two lines
       'of code below the ".Show" method are not executed

           If .Show Then

               'The following two lines will set Print Titles to the ranges
               'that are in the two edit boxes on the dialog sheet.

               'If the edit boxes are empty, then Print Titles are cleared.

               ActiveSheet.PageSetup.PrintTitleRows = _
                   .EditBoxes("rowrange").Text
               ActiveSheet.PageSetup.PrintTitleColumns = _
                   .EditBoxes("colrange").Text

           End If

       End With

   End Sub

To add the macro to a custom toolbar button

1. On the View menu, click Toolbars, and click the Customize tab.

2. Under Categories, click Custom at the bottom of the list.

3. Drag any one of the custom buttons to a visible toolbar, and when the Assign Macro dialog box appears, click the Set_Print_Titles macro, and then click OK.

4. Click Close in the Customize dialog box.

To use the macro

1. Activate your worksheet and click the custom button you created in the "To add the macro to a custom toolbar button" section of this article.

   The custom dialog box is displayed.

2. To set row and column print titles:

       a. Click the Rows To Repeat At Top box, and then select the row numbers
          that you would like to appear for print titles.
   

       b. Click the Columns To Repeat At Left box, and select the column
          letters for your print titles.
   

   NOTE: You can leave either box blank if you do not want to use row AND column titles.

3. Once you have made your selections, click the OK button.

   ARTICLE-ID: Q121401
   TITLE     : XL: Setting Print Area and Print Titles on Worksheet

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 11, "Controls and Dialog Boxes"