Chart Series drop down option

[jj72uk]

I have a chart which is designed using series data.

The X axis has 31 days, representing 31 days of the month, the series data is then links to various managers giving a percantage. With 9 managers it can look a bit busy.

What would be nice is to have a drop down box or a list box to allow me/my manager to select which series (or multiple series is poss) which we want.

I'm guessing vba is needed, my vba is very limited and i usually use the macro recorder to see how the vba is written to impliment it, in this case I wouldnt know where to start.

Any help?

[arthurbr]

Hi,
please post in the correct forum.It gives you a better chance on a quick answer I will move this one to the Charts forum for you

[Andy Pope]

See here for interactive charts
http://peltiertech.com/Excel/Charts/ChartByControl.html

[jj72uk]

Thanks Andy I have read many of your helpful tutorials before!

What I was hoping what to have the drop box box/list box within the Chart sheet, not a chart as a item on a sheet

[arthurbr]

Depending on your source data, a Pivot Chart should do the trick

[Andy Pope]

if you use Form controls they can be placed on a chart sheet

[jj72uk]

Hi Guys,

I read that tutorial, but am still confused.

Espically about the - =OFFSET(A5,0,$H$1)

I've created the list box, I have the right lists in the list box, I just need to somehow link it so when the names are clikc the chart changes

[Andy Pope]

can you post example of what you have so far

[jj72uk]

Here we go

[Andy Pope]

Paste following in a standard code module.
Assign macro to listbox.

Sub ListBox1_Click()

    Dim rngData As Range
    Dim lngIndex As Long
    Dim objList As ListBox
    
    Set rngData = Range("A2:A10")
    Set objList = ActiveSheet.ListBoxes("List Box 2")
    
    For lngIndex = 1 To objList.ListCount
        rngData.Rows(lngIndex).Hidden = Not objList.Selected(lngIndex)
    Next
    
End Sub

To do without hiding rows requries setting up of a chartdata table and individual checkboxes. If the checkbox is checked then display value other wise display #N/A in chart data table.

[jj72uk]

EDIT: OK...ive got it all working!

Apart from 1 thing...

When I go through the list box clicking on different names they all work.

Apart from when I click on 1 specific one it displays the graph correctly but the legend is messed up.

The legend seems to come from the x axis just on this one specfic one?

EDITS EDIT:

Fixed

[Andy Pope]

this is a pivot table version.


Which one is not working correctly?

[jj72uk]

I didnt think it was worth while making a new post as my question is on theback of this.

I have another excel sheet that I want to create a list box. I have used the above code, but chnaged the range to the correct one

Sub Listbox1_Click()
On Error GoTo Errorcatch

    Dim rngData As Range
    Dim lngIndex As Long
    Dim objList As ListBox
    
    Set rngData = Range("AHT source data!$f$5:$Ab$11")
    Set objList = ActiveSheet.ListBoxes("List Box 4")
    
    For lngIndex = 1 To objList.ListCount
        rngData.Rows(lngIndex).Hidden = Not objList.Selected(lngIndex)
    Next
    
Errorcatch:
MsgBox Err.Description
    
End Sub

It wouldn't work so I included an error catch and the error i keep getting is:

method 'range' of object '_worksheet' failed

Am i doing something drastically wrong?

[jj72uk]

EDIT:

I can get the list box to work if its on the same sheet as the data tables....it just seems if I add it to the chart sheet...

[Andy Pope]

You sheet name has spaces in it so requires single quotes wrapped around it

    Set rngData = Range("'AHT source data'!$f$5:$Ab$11")

[jj72uk]

Andy,

Still getting the same error with the below code.

Sub Listbox1_Click()
On Error GoTo Errorcatch

    Dim rngData As Range
    Dim lngIndex As Long
    Dim objList As ListBox
    
    Set rngData = Range("'AHT source data'!$f$5:$Ab$11")
    Set objList = ActiveSheet.ListBoxes("List Box 8")
    
    For lngIndex = 1 To objList.ListCount
        rngData.Rows(lngIndex).Hidden = Not objList.Selected(lngIndex)
    Next
    
Errorcatch:
MsgBox Err.Description
    
End Sub

[Andy Pope]

You need to post an example to demonstrate the problem.

[jj72uk]

Please find the attached sheet.

[Andy Pope]

Appears that a range reference that includes the sheet name does not work when triggered by clicking the listbox.
So break the sheet/range reference.

Sub Listbox1_Click()
On Error GoTo Errorcatch

    Dim rngData As Range
    Dim lngIndex As Long
    Dim objList As ListBox
    
    Set rngData = Worksheets("AHT source data").Range("$f$5:$Ab$11")
    Set objList = ActiveSheet.ListBoxes("List Box 8")
    
    For lngIndex = 1 To objList.ListCount
        rngData.Rows(lngIndex).Hidden = Not objList.Selected(lngIndex)
    Next
    Exit Sub
    
Errorcatch:
MsgBox Err.Description
    
End Sub