XL: Sample Macro to Set Print Titles in Active Worksheet (141093)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements.

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

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:
   
   
   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.

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