vba courses london - Pivot Tables

The UK's most regular instructor-led training courses.
Training information: vba courses london · Excel london

Resolved · Low Priority · Version Standard

Pivot Tables

Christopher has attended:
Excel VBA course

by - delegate Christopher [3 posts] (2007 Mar 27 Tue, 16:42) Reply

Is there a way to disable access to raw data on pivot tables?

RE: Pivot Tables

by - trainer Roy [23 posts] (2007 Mar 28 Wed, 12:25) Reply

Hi Christopher,
Yes, as I suggested, it turns out to be a settable property of a PivotTable object:

PivotTable.EnableDrilldown

e.g.

Sub ToggleDrilldown()

Dim pt As PivotTable
Dim status As Boolean

Set pt = Worksheets("PT Orders").PivotTables("PT_Orders")
status = pt.EnableDrilldown
pt.EnableDrilldown = Not (status)

End Sub

PivotTable has lots of properties! Might be worth a browse through the others.

/Roy

RE: Pivot Tables

by - delegate Christopher [3 posts] (2007 Mar 28 Wed, 12:27) Reply

That's fantastic - thanks Roy.

Chris

RE: Pivot Tables

by - delegate Christopher [3 posts] (2007 Mar 28 Wed, 12:37) Reply

I've found an easier way to do this. In the Table Options, you can uncheck the Enable Drill to Details box, protect the sheet and allow users to Use PivotTable reports.

· Company Roles in Visual Basic Training
· What Is Microsoft Excel VBA and How Can It Help You Work Smarter?

Vba tip:

Display Text In a Msgbox On Multiple Lines

When displaying text in a MsgBox you may need to show it over multiple lines. To do this use:

vbCrLf

As in:

MsgBox "The System has detected an error." & vbCrLf & "Contact your System Administrator"

The first sentence in quotes will appear above the second in the MsgBox.

View all VBA hints and tips