Contextures Blog

Yesterday, I started out with the best of intentions, planning to get some work done, and find a couple of topics for upcoming blog posts.

Then, while sipping my morning coffee and reading the RSS feeds, I clicked on an article about pivot tables.

Continue reading “Remove Pivot Table Calculated Field With Excel VBA”

If you’ve added comments to an Excel worksheet, you have a couple of built-in options for printing the comments.

• Show the comments on the worksheet, and print them as displayed.
• Print the list of comments at the end of the worksheet, on a separate printed page.

Printing Comments Shown on Sheet

Printing the comments on the worksheet is okay if there are only a couple of comments, and you can arrange them so they don’t cover the data.

Print List of Comments

For more than a couple of comments, the list at the end of the worksheet is a better choice.

However, with the built-in list printing option, you just get the cell address and comment, printed in a long, single column.

Create Your Own List of Comments

Instead of using the built-in list of printed comments, you can use a macro to create your own list of comments on a separate worksheet, and print that list.

It’s also a great way to review all the comments on a worksheet, and use sorting or filtering to focus on specific comments.

See the Comment Printing VBA Code

Shown below is the Excel VBA code to create a list of comments from the active sheet, written by Dave Peterson.

For more comment programming examples, including Dave’s code to list all the comments in the entire workbook, see Excel Comments VBA.

The Comment List Code

The ShowComments macro adds a new sheet to the workbook, and lists all the comments, the comment author name, and the comment cell’s value, address and name (if any).

At the end of the macro, the first row is formatted in bold font, and the column widths are autofit.

Sub ShowComments()
'posted by Dave Peterson
    Application.ScreenUpdating = False
Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Set curwks = ActiveSheet
On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If
Set newwks = Worksheets.Add
newwks.Range("A1:E1").Value = _
Array("Address", "Name", "Value", "Author", "Comment")
i = 1
For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = mycell.Address
.Cells(i, 2).Value = mycell.Name.Name
.Cells(i, 3).Value = mycell.Value
.Cells(i, 4).Value = mycell.Comment.Author
.Cells(i, 5).Value = mycell.Comment.Text
End With
Next mycell
With newwks
.Rows(1).Font.Bold = True
.Cells.EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub

_____________

Last week, I posted Bob Ryan’s Excel macro for formatting a pivot table in Classic style. Bob’s macro formats the first pivot table indexed on the active sheet.

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)

Modify the Code

Ideally, you’d only have one pivot table on a worksheet, to prevent problems with overlapping, and Bob’s code would work very well. However, as you know, life in Excel isn’t always ideal!

Let’s look at a few scenarios, and how to modify the macro to deal with them.

Select a Pivot Table

In the blog post comments, Yard suggested a variation on the code, so the macro would run on the selected pivot table, to accommodate worksheets with multiple pivot tables.

If a cell in a pivot table isn’t selected, an “Oops” message would be displayed.

    On Error Resume Next
    Set PT = ActiveCell.PivotCell.PivotTable
    On Error GoTo 0
    If PT Is Nothing Then
        MsgBox "No PivotTable selected", vbInformation, "Oops..."
        Exit Sub
    End If

Thanks, Yard, for your sample code. On a multiple pivot table sheet, the user can control which pivot table is formatted.

Format All Pivot Tables on Active Sheet

Taking that idea a bit further, let’s assume you have a worksheet with several pivot table on it. With Yard’s code, shown above, you could select a cell in one of those pivot tables, and run the macro to format that pivot table only.

But, what if you wanted to format all the pivot tables on that sheet? It would take a while to select each pivot table, and run the macro. Instead, you could modify the code, so it formats all the pivot tables on the active sheet.

    For Each PT in ActiveSheet.PivotTables
        'the formatting code goes here
    Next PT

Format All Pivot Tables on All Worksheets

Finally, what can you do if there’s more than one worksheet with pivot tables? You don’t want to waste time selecting each worksheet, and running the macro to format all the pivot tables on that sheet.

To loop through the worksheet, you could modify the code, so it formats all the pivot tables on each worksheet in the active workbook.

    Dim ws as Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For Each PT in ws.PivotTables
            'the formatting code goes here
        Next PT
    Next ws

______________