VBA test for Subtotal (Excel 97 SR2)

Topic

New Reply

I have a macro that is a toggle for Filtering. It tests to see if Filtering is on. If Filtering is on it turns it off. If Filtering is off, it turns it on.
I am interested in a similar macro that will toggle SubTotals, but I do not know how to test if SubTotaling is on. I could not find a property for SubTotal. I can just use two buttons, but would prefer to have one button that would toggle Subtotal on/off.
Any ideas?

Thanks,
Chuck

Reply | Quote

Replies

There are several problems here.

1. Whether a worksheet has subtotals is not exposed directly in the object model. The following kludge may help; it is not 100% dependable, though. It looks for the presence of “subtotal(*)” where * is any number of characters. If found, it is most likely to be a worksheet formula with the SUBTOTAL function, but it could be a label.

Function HasSubtotal() As Boolean
HasSubtotal = Not (ActiveSheet.Cells.Find(What:=”subtotal(“, LookIn:=xlFormulas, _
LookAt:=xlPart, MatchCase:=False, SearchFormat:=False) Is Nothing)
End Function

2. Turning subtotals off is easy:

Range(“A1”).RemoveSubtotal

but you can’t just turn subtotals on, you have to specify exactly how, so you will have to tailor it for your situation.

Here is an example:

Sub Macro1()
If HasSubtotal Then
Range(“A1”).RemoveSubtotal
Else
Range(“A1”).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End If
End Sub

This will add/remove subtotals on column B and C where column A changes.

Reply | Quote

Hans,
Thanks for the reply. I had not thought of creating a function. I thank you very much.
I will see if I can use your suggestion. I already have a macro that sets up the subtotals the way I want, so I should not have any trouble using your technique.
I was considering having my Subtotal macro enter a number in a cell when it is run, then test the contents of that cell. I like your way better.
Thanks again,
Chuck

Reply | Quote

Hans,
I am having trouble getting it to work. I get a Run-time error ‘448’: Named argument not found.
I attached a workbook with the function and macro in place.
Any ideas?

Thanks,
Chuck

Reply | Quote

Sorry, I should have anticipated that – the SearchFormat argument is not available in Excel 97. Just remove the
, SearchFormat:=False
part. By the way, since column B in your worksheet is text, it doesn’t make sense to have a subtotal on it. So replace TotalList:=Array(2, 3) (subtotals on the 2nd and 3rd column) with TotalList:=Array(3) (subtotals on the 3rd column only).

Reply | Quote

Hans,
That did the trick. It works and I can use just one button instead of two.
Thanks for sharing your gift.

Chuck

Reply | Quote