Excel 2007 Upgrade FAQ: Formatting And Printing

Category: General / Formatting / Printing | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q: How do I get my old workbook to use the new fonts?

A: Press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scrollbar in Styles gallery, and choose Merge Styles. In the Merge Styles dialog box double-click the new workbook you created with Ctrl+N. But this only works with cells that have not been formatted. For example, bold cells retain their old font.

Q. How do I get a print preview?

A. Try using the Page Layout view (icon on the right side of the status bar). Or, add the Print Preview button to your QAT.

Q: When I switch to a new document template, my worksheet no longer fits on a single page.

A: That's probably because the new theme uses different fonts. After applying the theme, use the Page Layout / Themes / Fonts control to select your original fonts to use with the new theme. Or, modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.

Q: How do I get rid of the annoying dotted-line page break display in Normal view mode?

A: Open the Excel Options dialog box, click the Advanced tab, scroll down and look for the 'Display options for this worksheet' section, and remove the checkmark from 'Show Page Breaks'.

Q: Can I add that 'Show Page Breaks' option to my QAT?

A: No. For some reason, this very useful command isn't available as a QAT icon.

Q: I changed the text in a cell to use Angle Clockwise orientation (in the Home / Alignment group). I can't find a way to get the orientation back to normal. There's no Horizontal Alignment option.

A: To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.

Q. I'm trying to apply a table style to a table, but it has no effect.

That's probably because the table cells were formatted manually. Remove the old cell background colors, and applying a style should work.

Q: I thought Office 2007 was supposed to support PDF output. I can't find the command.

A: You need to download a free add-in from Microsoft. Blame the Adobe attorneys. After you download and install the add-in, click the Office Menu button and then select Save As / PDF or XPS.

Determining The Number Of Printed Pages

Category: Printing / General VBA | [Item URL]

If you need to determine the number of printed pages for a worksheet printout, you can use Excel's print preview feature, and view the page count displayed at the bottom of the screen.

This tip provides two ways to determine the number of printed pages -- one using the the Excel 4 (XLM) Get.Document macro function, the other using VBA.

Using an XLM macro in VBA

You can execute an XLM macro function from VBA, as follows:

  PgCnt = ExecuteExcel4Macro("Get.Document(50)")

In the statement above, the number of printed pages in the active sheet is assigned to the PgCnt variable.

The VBA subroutine below loops through all worksheets in the active workbook and displays the total number of printed pages. Note that this may return incorrect results when using a user-specified print range.

Sub ShowPageCount()
    PageCount = 0
    For Each sht In Worksheets
        sht.Activate
        Pages = ExecuteExcel4Macro("Get.Document(50)")
        PageCount = PageCount + Pages
    Next sht
    MsgBox "Total Pages = " & PageCount
End Sub

Using VBA

Stew Scott provided the VBA procedure below, which does not use XLM.

Sub NumberOfPrintedPages()
    Worksheets(1).DisplayAutomaticPageBreaks = True
    HorizBreaks = Worksheets(1).HPageBreaks.Count
    HPages = HorizBreaks + 1
    VertBreaks = Worksheets(1).VPageBreaks.Count
    VPages = VertBreaks + 1
    NumPages = HPages * VPages
    Worksheets(1).DisplayAutomaticPageBreaks = False
    MsgBox NumPages
End Sub

Mail Merge - Without Word

Category: Printing / General VBA | [Item URL]

A companion file is available: Click here to download

Mail merge is the process of merging information from a database into a text document, and then printing the document. The result is a series of personalized documents.

Normally, Microsoft Word is used for this type of operation. Word can perform a mail merge with data stored in an Excel workbook. The process works well, but in some cases it may be preferable to eliminate Word and do all of the work with Excel.

The basics

Using Excel to perform a mail merge requires:

• A range that contains the data to be merged. In the example, this is on the sheet named Data.
• A range that contains the text. In the example, this is on the sheet named Form.
• A cell that contains the row number of the "current record." In this example, that cell is C3.
• Formulas that use Excel's INDIRECT function to retrieve data from the current record. These formulas are in column L.
• Formulas within the text that refer to the cells in column L. The example uses several such formulas to personalize the letter.
• A simple macro that loops through the data, incrementing the current record number and printing the sheet.

This example, of course, can be adapted to many other purposes.

This example has a few additional features:

• The ability to specify the first and last records from the database
• Buttons to assist in navigating through the workbook
• An option to preview instead of print
• A Help button that displays instructions

Displaying A Menu Of Worksheets To Print

Category: Printing / UserForms | [Item URL]

This tip contains a VBA subroutine that displays a dialog box that contains the names of all non-empty worksheets in the active workbook. The names are displayed as checkboxes. The user can select which sheets to print, and click OK. The macro then prints the selected worksheets.

This subroutine is rather unusual since it creates the dialog box on the fly, and then deletes it after it is dismissed.

The code below prints the selected worksheets, but this general technique can be adapted to other situations in which you need the user to specify one or more sheets in a workbook.

Although this procedure uses an Excel 5/95 Dialog Sheet, it will also work with later versions of Excel. By the way, this is a rare example of how using a Dialog Sheet is easier than using a UserForm. Creating a UserForm on the fly is much more difficult, but it is possible.

Using the code

To try out the example, copy the code below and paste it to a VBA module (there is no need to add a dialog sheet). Then execute the SelectSheets subroutine.

The SelectSheets subroutine

Option Explicit

Sub SelectSheets()
    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Application.ScreenUpdating = False

'   Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If

'   Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

'   Add the checkboxes

    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
'       Skip empty sheets and hidden sheets
        If Application.CountA(CurrentSheet.Cells) <> 0 And _
            CurrentSheet.Visible Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = _
                    CurrentSheet.Name
            TopPos = TopPos + 13
        End If
    Next i

'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Select sheets to print"

    End With

'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Activate
                    ActiveSheet.PrintOut
'                   ActiveSheet.PrintPreview 'for debugging

                End If
            Next cb
        End If
    Else
        MsgBox "All worksheets are empty."
    End If

'   Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

'   Reactivate original sheet
    CurrentSheet.Activate
End Sub

NOTE: Aaron Blood suggested the following modification, which prints the selected sheets as a single print job. This allows the sheet to be printed with continuous page numbers.

    If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Select Replace:=False 
                End If
            Next cb
            ActiveWindow.SelectedSheets.PrintOut copies:=1 
            ActiveSheet.Select 
    End If

Copy Page Setup Settings To Other Sheets

Category: Printing | [Item URL]

Each Excel sheet has its own print setup options (orientation, margins, headers and footers, and so on). These options are specified in the Page Setup dialog box, which you access using the File, Page Setup command.

When you add a new sheet to a workbook, it contains the default page setup setting. Here's an easy way to transfer the settings from one worksheet to additional worksheets:

1. Activate the sheet that contains the desired setup info. This is the "source" sheet.
2. Select the "target" sheets. Press Ctrl and click the sheet tabs of the sheets you want to update with the settings from the source sheet.
3. Select File, Page Setup and click OK.

The Page Setup settings of the source sheet will be transferred to all of the target sheets.

Printing Just A Portion Of Your Worksheet

Category: Printing | [Item URL]

If you need to print just a small portion of your worksheet:

1. Select the cells that you want to print
2. Choose File - Print
3. In the Print dialog box, choose the Selection option
4. Click OK

If you've specified a print area, this will not change it.

Note: If your selection consists of a non-contiguous range (a multiple selection), each area is printed on a separate sheet of paper.

Avoid Printing Specific Rows

Category: Printing | [Item URL]

In some cases, you may wish to avoid printing certain rows in your worksheet. For example, the rows may contain confidential information, or intermediate results that need not be printed.

To avoid printing specific rows, you can hide the rows before you print, and then unhide the rows after printing. If your sheet has many rows that should not be printed, hiding and unhiding the rows may be a tedious process. This tip presents a way to quickly toggle the hidden status of any number of rows. It takes advantage of Excel's Group and Outline feature.

The figure below shows a simple example. In this case, rows 5, 10, 15, and 20 should not be printed.

To set up a simple outline, follow these steps:

1. Select Row 5
2. Choose Data - Group and Outline - Group (or, use Alt+Shift+RightArrow)
3. Select Row 10
4. Press F4 (this key repeats the last command)
5. Select Row 15
6. Press F4
7. Select Row 20
8. Press F4

The preceding steps created a simple outline on the worksheet, and the outline symbols are displayed along the left side of the sheet. You can hide all of the "grouped" rows by clicking the small "1" button at the top of the outline symbol area (see the figure below).

After you've printed the sheet, click the "2" button to redisplay all of the rows.

Note: To hide the outline symbols, press Ctrl+8. The outline remains, but the symbols are hidden. To re-display the outline symbols, press Ctrl+8 again.