Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA test for Subtotal (Excel 97 SR2)

    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
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA test for Subtotal (Excel 97 SR2)

    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.

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA test for Subtotal (Excel 97 SR2)

    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
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA test for Subtotal (Excel 97 SR2)

    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
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA test for Subtotal (Excel 97 SR2)

    Sorry, I should have anticipated that - the SearchFormat argument is not available in Excel 97. Just remove the
    <code>, SearchFormat:=False</code>
    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 <code>TotalList:=Array(2, 3)</code> (subtotals on the 2nd and 3rd column) with <code>TotalList:=Array(3)</code> (subtotals on the 3rd column only).

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA test for Subtotal (Excel 97 SR2)

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

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •