Hiding the subtotal button in OWC

[Nitin]

Is it possible to hide the subtotal button in the OWC toolbar
programmatically?



We are using this code to hide totals. However, if a user clicks on
the subtotal button, the totals reappear. Is there a way to prevent
this?



Private Sub PivotTable1_BeforeQuery()

Dim fs As Variant

Dim f As Variant



For Each fs In PivotTable1.ActiveView.FieldSets

For Each f In fs.Fields

f.Subtotals(0) = False

Next f

Next fs

End Sub



Thanks in advance!

Nitin

 

[Dan Ricker]

If you are using OWC10 (OfficeXP) or OWC11 (Office2003)
then yes the toolbar button can be hidden. Note that you
will also need to deal with the corresponding menu item
for sub totals. The easiest way to deal with both is
actually to disable the "Commands". This won't hide the
button/menu item but will disable them.

''' Warning... Sample VBScript Air Code
''' Use the CommandEnabled event
Sub PivotTable1_CommandEnabled(Command, Enabled)
Dim objC
Set objC = PivotTable1.Constants
If IsNumberic(Command) Then
If Command = objC.plCommandAutoAverage Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoCount Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoMax Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoMin Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoStdDev Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoStdDevP Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoSum Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoVar Then
Enabled.value = False
ElseIf Command = objC.plCommandAutoVarP Then
Enabled.value = False
End If
End If
End Sub

**************************************************
You can also remove the Toolbar button from the Toolbar:

PivotTable1.Toolbar.Buttons(X).Remove
''' X = button name or index to be removed

But that still leaves the Context menu Menu to be dealt
with.

This would need to be done using the:

Event BeforeContextMenu(x As Long, y As Long, Menu As
ByRef, Cancel As ByRef)

When the event is fired, the Menu.Value (an Array of
Arrays) would need to be searched for the Subtotal item.
The Array of Arrays would need to be rebuilt to exclude
this array item then Menu.Value set to the new rebuilt
array of arrays of menu items.