Microsoft KB Archive/141093

= XL: Sample Macro to Set Print Titles in Active Worksheet =

Article ID: 141093

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a for Macintosh
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q141093



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. 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.
 * 1) Rename the dialog sheet titles.
 * 2) In the dialog sheet, create two edit boxes, and position the boxes one above the other. (Leave room for a label above each box.)
 * 3) Give the edit boxes the defined names rowrange and colrange.
 * 4) Select the "rowrange" edit box, and click Object on the Format menu.
 * 5) Select the Control tab. Under Edit Validation, select Reference. Click OK.
 * 6) Repeat steps 5 and 6 for the "colrange" edit box.
 * 7) 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
 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. To set row and column print titles:

 Click the Rows To Repeat At Top box, and then select the row numbers that you would like to appear for print titles. 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.  Once you have made your selections, click the OK button.</ol>

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

121401 XL: Setting Print Area and Print Titles on Worksheet

<div class="references_section">