XL: Sample Macro to Set Print Titles in Active Worksheet

ID: Q141093

The information in this article applies to:

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 professionals 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/

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.

For additional information, please see the following article in the Microsoft Knowledge Base: 

   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"

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a XL7 XL5 

Keywords          : kbcode kbprg PgmHowto 
Version           : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999