Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Pivottable Objects with VBA (Office 97)

    Hi!

    I have a spreadsheet with several pivottables and some VBA that performs various operations on them, including changing the source of data and resetting the formatting.

    One of the parts of the pivottable that I want to reformat is the row subtotals, but I can't work out how to specify the object.

    As an example, I can use the following code to format the rows themselves:
    <pre> objSheet.PivotTables(1).RowFields("Field1").DataRa nge.Interior.ColorIndex = 15</pre>


    How can I do that kind of thing to subtotals?

    Hope that's clear. Thank you to anyone who responds.

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

    Re: Formatting Pivottable Objects with VBA (Office 97)

    In Excel 2002 you can do something like this, but I don't know whether it works in Excel 97:

    Application.PivotTableSelection = True
    ActiveSheet.PivotTables(1).PivotSelect "Field1[All;Total]", xlDataAndLabel, True
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With

  3. #3
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Pivottable Objects with VBA (Office 97)

    Yes, it does work in Excel 97, but only if you remove the ", True" argument at the end.

    I was really hoping to modify the object directly, rather than have to make a selection, but it really does look like that's the only solution.

    Thanks you, Hans!

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

    Re: Formatting Pivottable Objects with VBA (Office 97)

    In general, I try to avoid selecting cells in a macro, but I don't think it can be avoided here - there doesn't appear to be an equivalent of RowRange or PageRange for (sub)totals in the object model.

Posting Permissions

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