I am using Excel 2007 and my version of visual basic is 6.5.
I am baffled by the behavior of this code to manipulate one of my pivot tables. I am trying to set all but one of the pivot items in one of the pivot fields to not visible. Because there is a large number of items, I wish to suspend all automatic updates until all items are properly set to visible or non visible.
------------------------------------------------------
Sub SwitchBoards()
Dim BoardNew As String
Sheets("Board Parameters").Select
' Make sure we get the right value.
Calculate
BoardNew = Range("Status!N3")
' Update!
Call UpdatePivotTable("Parameters", "DailyM", "BOARD_KEY", BoardNew)
.
.
.
Calculate
End Sub
------------------------------------------------------
Sub UpdatePivotTable(PivSheet As String, PivTable As String, PivField As String, PivItem As String)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(PivTable)
With pt
Set pf = .PivotFields(PivField)
.PivotCache.Refresh
End With
' Suspend updates till further notice.
pt.ManualUpdate = True
'
MsgBox "Manual update is set to " & pt.ManualUpdate
'
' Set the one we want to visible, if it wasn't already.
Set pi = pf.PivotItems(PivItem)
If Not pi.Visible Then pi.Visible = True
' Set the rest to invisible.
For Each pi In pf.PivotItems
If pi <> PivItem And pi.Visible Then pi.Visible = False
Next pi
' Reset.
pt.PivotCache.Refresh
End Sub
------------------------------------------------------
The MsgBox returns with: "Manual update is set to False" right after the instruction to set it to True!! Can anybody educate me as to why manualupdate is not being set the way I want??
Thanks in advance.
Bookmarks