PivotTableWoes

  • Thread starter Thread starter icestationzbra
  • Start date Start date
I

icestationzbra

i have a pivottable on a sheet which is protected thru a macro. is there
a way to refresh the pivottable data upon workbook_open, via macro?
ideally, i would not like to pass the control of refreshing the
pivottable data out to the user. if it is not possible, is there a way
to protect the pivottable structure? user could be allowed to refresh
data by clicking on the pivottable, but, they should not be able to
click and drag fields.

thanks in advance,

mac.
 
Right click on the Pivot table and choose Table Options - Then just check the
'Refresh on opening' option.
 
You could add a 'Refresh' button to the sheet that contains the
PivotTable, with code to unprotect the sheet, refresh the table, and
reprotect the sheet.
 
Oops! I misread your question. In the Workbook_Open code, you could
refresh all PivotTables in the workbook, then protect the sheets:

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="MySecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="MySecretWord"
Next

End Sub
 
debra,

i used the code posted by you. the only thing i was doing differentl
was that i was calling the refreshpivottable directly from the sheet
Sheet1.PivotTables("SheetName").RefreshTable. i had even tried the on
provided in ExcelHelp. it did not work.

however, i had to use your code in conjunction with another piece tha
i found elsewhere on this forum.

*****
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
*****

if this is run before your code, the refresh happens just fine. else
the legacy data are not forgotten.

thank you for your help.

mac
 
hi debra,

i needed some help with pivottable.

the sheet which houses the pivottable is protected.

i would like the user to be able to use the filter but not drag an
column out of its place. basically, dropdownlist should be the onl
thing that should work in terms of data manipulation. however, use
should also be able to select data and paste it onto another workbook


if there are any resources out there, on your site or elsewhere, coul
you please point me to them? or if you could tell me which are all th
parameters that need to be toggled true/false for this, i wil
certainly appreciate it.

thanks,

mac
 
If you have Excel 2002, you can allow PivotTable use when you protect
the sheet:

Worksheets("Pivot").Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True

To lock up the PivotTable, you can use code similar to the following:
'===========================
Sub PTSettings()
Dim pf As PivotField
With Worksheets("Pivot").PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.PivotCache.EnableRefresh = True
For Each pf In .PivotFields
pf.DragToPage = False
pf.DragToRow = False
pf.DragToColumn = False
pf.DragToData = False
pf.DragToHide = False
Next pf
End With
End Sub

'=======================
 
hi debra,

thanks for that piece of code. it worked great. i am able to do what
intended to.

thanks for the help.

regards,

mac
 
hi debra,

sorry to bother you about this issue again.

i have office 2002 xp on my machine. hence, all the code you had
posted, ran smoothly.

however, the client machine has xl 2000. some of the pieces flipped.

i know you had mentioned that the code holds good only for xl 2002. i
would just like to know if there is another way of handling this
situation, with an add-in or something.

some parameters like allowusingpivottable and allowfiltering do not
work.

thanks for your time.

mac.
 
Back
Top