XL: Sample Macro to Set Print Titles in Active Worksheet

MORE INFORMATION

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.
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.
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, and for the bottom label, type Columns to Repeat at Left.

To create the macro

      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:
   
   
   1. Click the Rows To Repeat At Top box, and then select the row numbers that you would like to appear for print titles.
   2. 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.