Toggle Subtotal on and off

[Paul D. Simon]

I'm trying to create a button on a worksheet that will "toggle"
Subtotal on and off. I came up with this code, but it doesn't work.
The 2nd line (If Selection.SubTotal = False Then) is the offending
line. What would be the correct syntax?

Sub SubtotalOnOff()
Range("A8").Select

If Selection.SubTotal = False Then

Selection.SubTotal GroupBy:=3, Function:=xlSum,
TotalList:=Array(5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Else

Selection.RemoveSubtotal

End If

End Sub

 

[NickH]

Paul,

Because Subtotal is a Method rather than a property it doesn't have a
'state' that you can check in this way. A fairly easy way round this
would be to toggle something else at the same time as you apply or
remove Subtotal e.g. the forecolor of the button font or maybe the
button caption from [ Apply Subtotals ] to [ Remove Subtotals ]. This
would then give you a 'state' to check at the top of your routine.

Alternatively how about creating a name e.g. SubtotalState that you
toggle from ="ON" to ="OFF"


Sub SubtotalOnOff()

If ActiveWorkbook.Names("SubtotalState").RefersTo = "=""OFF""" Then


Range("A8").SubTotal GroupBy:=3, Function:=xlSum,
TotalList:=Array(5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveWorkbook.Names("SubtotalState").RefersTo = "=""ON"""

Else

Selection.RemoveSubtotal
ActiveWorkbook.Names("SubtotalState").RefersTo = "=""OFF"""

End If

End Sub


I've not tested this but it should work - don't forget, you will need
to create the name first.

HTH
NickH

 

[Paul D. Simon]

Nick,

ABSOLUTELY BRILLIANT! Works perfectly! Thank you so very much!

Paul