Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1054
  • Last Modified:

VBA - Pivot Table Formatting

Experts,

I am looking to format each pivotitem in field 3 (I5:I14) where each "group", such as I5:I6 and I7:I8 is formatted as displayed. Which is a thin horizontal line between each, then the group is outlined in the medium weight border. The example of this is I5:I8 on the attached worksheet.

Then I am also looking to apply the same formatting to field 4, with J5:J6 being formatted "thin horizontal line", then "medium weight - borderaround).

When using the PivotItem (such as PI3.LableRange.Borders or PI3.LableRange.BorderAround) I can not get the inside horizontal to work and also change the weight to thin. I only seem to be able to turn on the inside horizontal or adjust the weight, but not both.

Should borders or border around be used on this?

Thank you,
Kevin

Option Explicit
Sub FormatSalesByProduct()

Dim PT As PivotTable
Dim PF1 As PivotField, PF2 As PivotField, PF3 As PivotField, PF4 As PivotField
Dim PI1 As PivotItem, PI2 As PivotItem, PI3 As PivotItem, PI3 As PivotItem

    Set PT = ActiveSheet.PivotTables(1)
    Set PF1 = PT.RowFields(1)
    Set PF2 = PT.RowFields(2)
    Set PF3 = PT.RowFields(3)
    Set PF4 = PT.RowFields(4)
    On Error Resume Next
        For Each PI1 In PF1.VisibleItems
            PI1.LabelRange.BorderAround _
                Weight:=xlMedium
            For Each PI2 In PF2.PivotItems
                Intersect(PI2.LabelRange, PI1.LabelRange.EntireRow).BorderAround _
                    Weight:=xlMedium
            Next PI2
        Next PI1
        
        For Each PI3 In PF3.VisibleItems
            PI3.LabelRange.BorderAround (xlInsideHorizontal) _
                Weight:=xlThin
            PI3.LabelRange.BorderAround _
                Weight:=xlMedium
            Next PI3
        
        For Each PI4 In PF4.VisibleItems
            PI4.LabelRange.BorderAround (xlInsideHorizontal, xlthin)
            PI4.LabelRange.BorderAround _
                Weight:=xlMedium
            Next PI4
End Sub

Open in new window

EE-PivotTable-Format-Fields3and4.xls
0
redrumkev
Asked:
redrumkev
  • 2
1 Solution
 
telyni19Commented:
I don't think xlInsideHorizontal will work with BorderAround. BorderAround sets the outside border around the specified item or range, while xlInsideHorizontal controls the borders between multiple cells within a range. Also, if you only have one cell specified at a time, xlInsideHorizontal won't do anything. It looks like PI3 is only selecting one cell at a time, the way you have it set up right now. You'll want to use Borders on the full range of cells where you want thin lines inside and then BorderAround on the same range to put the medium line around them.
0
 
redrumkevAuthor Commented:
telyni19,

What about selecting the item in field 2, then offset to the right, this should have 2 cells selected. Then xlInsideHorizontal, followed by BorderAround, then go to the next field 2 item (again offset to right).

Do know if there is a property that is calls that data for a particular item? In my example, PLAN A is occupying rows 5 and 6, thus is there a way to reference "all data" for the first item?

Notice that PLAN C occupies row 7 through 10, so I don't want to go with a static 2 row setup, a more dynamic approach based off the pivot, I think would make sure it works no matter how to alter the pivot over time. Ideas?

Kevin
0
 
Rory ArchibaldCommented:
Does this do what you want?
Sub FormatSalesByProduct()

Dim PT As PivotTable
Dim PF1 As PivotField, PF2 As PivotField, PF3 As PivotField
Dim PI1 As PivotItem, PI2 As PivotItem

    Set PT = ActiveSheet.PivotTables(1)
    Set PF1 = PT.RowFields(1)
    Set PF2 = PT.RowFields(2)
    Set PF3 = PT.RowFields(3)
    On Error Resume Next
    For Each PI1 In PF1.VisibleItems
        PI1.LabelRange.BorderAround _
            Weight:=xlMedium
        For Each PI2 In PF2.PivotItems
            Intersect(PI2.LabelRange, PI1.LabelRange.EntireRow).BorderAround _
                Weight:=xlMedium
            With Intersect(PF3.DataRange, PI2.LabelRange.EntireRow, PI1.LabelRange.EntireRow)
                .Borders(xlInsideHorizontal).Weight = xlThin
                .BorderAround Weight:=xlMedium
            End With
            With Intersect(PT.DataBodyRange, PI2.LabelRange.EntireRow, PI1.LabelRange.EntireRow)
                .Borders(xlInsideHorizontal).Weight = xlThin
                .BorderAround Weight:=xlMedium
            End With
        Next PI2
    Next PI1
End Sub

Open in new window

0
 
redrumkevAuthor Commented:
Rorya,

This was EXACTLY what I wanted to do!

Thank you very much!
Kevin
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now