none
If...Then not working RRS feed

  • Question

  • Can someone help with this code?  I have it on a button in an excel spreadsheet, and I'm trying to use it to toggle the expand/collapsed option on a field in a pivot table.

    When I try to run it, I get "Application defined or object defined error"

    Sub expandFields()
    '
    ' Toggle File field between expand and collapsed
    
        If (ActiveSheet.PivotTables("FilePivotTbl").PivotFields("Division").ShowDetail) Then
            ActiveSheet.PivotTables("FilePivotTbl").PivotFields("Division").ShowDetail = False
        Else
            ActiveSheet.PivotTables("FilePivotTbl").PivotFields("Division").ShowDetail = True
        End If
    End Sub


    Thursday, August 27, 2015 7:45 PM

Answers

  • I was able to accomplish it with this:

    Sub MainPivotTbl_expandFields()
    '
    ' Toggle Division field between expand and collapsed
        
        ActiveSheet.PivotTables("MainPivotTbl").PivotFields("Activity").ShowDetail = True
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
        selection.OnAction = "MainPivotTbl_collapseFields"
        selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Collapse Details"
        Range("A1").Select
    End Sub
    
    
    Sub MainPivotTbl_collapseFields()
    '
    ' Toggle Division field between expand and collapsed
    
        ActiveSheet.PivotTables("MainPivotTbl").PivotFields("Activity").ShowDetail = False
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
        selection.OnAction = "MainPivotTbl_expandFields"
        selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Expand Details"
        Range("A1").Select
    End Sub

    Not as pretty as I'd like, but it works.

    • Marked as answer by Casey_M Tuesday, September 1, 2015 7:11 PM
    Tuesday, September 1, 2015 7:10 PM

All replies

  • As far as I can tell, you have to expand/collapse the individual pivot items of the pivot field:

    Sub expandFields()
        Dim pi As PivotItem
        For Each pi In ActiveSheet.PivotTables("FilePivotTbl").PivotFields("Division").PivotItems
            pi.ShowDetail = Not pi.ShowDetail
        Next pi
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 27, 2015 9:18 PM
  • Hello,

    If you use ShowDetail, a new sheet will open and your sheet with your pivot will no longer be the active one.

    Avoid this like activeSheet and reference it by reference.

    Hope it helps,

    Wouter

    Friday, August 28, 2015 9:35 AM
  • I was able to accomplish it with this:

    Sub MainPivotTbl_expandFields()
    '
    ' Toggle Division field between expand and collapsed
        
        ActiveSheet.PivotTables("MainPivotTbl").PivotFields("Activity").ShowDetail = True
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
        selection.OnAction = "MainPivotTbl_collapseFields"
        selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Collapse Details"
        Range("A1").Select
    End Sub
    
    
    Sub MainPivotTbl_collapseFields()
    '
    ' Toggle Division field between expand and collapsed
    
        ActiveSheet.PivotTables("MainPivotTbl").PivotFields("Activity").ShowDetail = False
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
        selection.OnAction = "MainPivotTbl_expandFields"
        selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Expand Details"
        Range("A1").Select
    End Sub

    Not as pretty as I'd like, but it works.

    • Marked as answer by Casey_M Tuesday, September 1, 2015 7:11 PM
    Tuesday, September 1, 2015 7:10 PM