PivotItem Delete deletes ActiveX Controls?

G

GoatieEddie

Hi,

I have an infuriating problem.

I loop through all of my PivotItems in a PivotTable in order to clea
out the cache.

However, when the macro runs, it also deletes the CheckBoxes, Optio
Buttons and Buttons on the same worksheet.

Is this something anyone else has come across and if so hwo can I sto
it happening? It is driving me mad and I am assuming it is an Exce
bug.

Sub DeleteOldItemsFromPivotTable()
'gets rid of unused items in PivotTable
' based on MSKB (202232)

Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next

Application.ScreenUpdating = True
End Sub

Cheers,

G
 
Top