One chart at a time..?

[kenjoswe]

Hi all,

I have a pivotchart that shows the development of 25 customers. My problem is that it's very difficult to see anything with all lines criss-crossing each other.

If I choose one customer at a time there's no problem with visiability. What I need is a macro, connected to a button, that steps from 1:st customer to the last and another button from the last customer to the first. Then I can analyze the development of every customer with just one click.

How should I write the syntax? My version is Excel2002.

/Kent J.

 

[SkipVought]

Hi,

Turn on your macro recorder.

Select an item in the PT.

Turn off the recorder and check your code.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[kenjoswe]

Skip,

This is how the recorded macro looks like with some customers:
======================================================
With ActiveChart.PivotLayout.PivotTable.PivotFields("OrgnrNamn")
.PivotItems("Abeko Konfektions AB").Visible = True
End With

With ActiveChart.PivotLayout.PivotTable.PivotFields("OrgnrNamn")
.PivotItems("Abeko Konfektions AB").Visible = False
.PivotItems("Akademibokhandelsgruppen AB").Visible = True
End With
========================================================
1.) If I run this macro then it will finish in 0.001 second - not much help. I want to run this step-by-step, one cutomer for each click on the button.
2.) The customernames should be dynamic so that I don't have to recode everytime with new data.
3.) Must I declare the name of the PivotField?

/Kent J.

 

[SkipVought]

It can be as simple as a Data/Verification - List in one cell with your CustomerList.

In the Worksheet_Change event in the Sheet Object containing the PivotTable,

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pvf As PivotField, pvi As PivotItem
    With ActiveChart.PivotLayout.PivotTable.PivotFields("OrgnrNamn")
        .PivotItems(Target.Value).Visible = True
        For Each pvi In .PivotItems
            If pvi.Value = Target.Value Then pvi.Visible = False
        Next
    End With
End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[kenjoswe]

Thanks!
I copied and pasted the syntax from the VBA editor.
But when I try to run this macro I'm promted to set a macroname. How can I trigger this?

/Kent J.

 

[SkipVought]

1) it must be pasted in the SHEET OBJECT and not a module

2) you do not run this macro. it runs when you CHANGE the cell containing your dropdown selection.

3) the procedure need a bit more tweeking to "connect" it to your process. I have a named range, SelectedCustomer, that has your drop down selection.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range([b]SelectedCustomer[/b])) Is Nothing Then
        Application.EnableEvents = False
        Dim pvf As PivotField, pvi As PivotItem
        With ActiveChart.PivotLayout.PivotTable.PivotFields("OrgnrNamn")
            .PivotItems(Target.Value).Visible = True
            For Each pvi In .PivotItems
                If pvi.Value = Target.Value Then pvi.Visible = False
            Next
        End With
        Application.EnableEvents = True
    End If
End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}