Enhance VBA code to change categories/legend and not just chart type! Please see attached workbook.

There are several ways to do this.  I just stayed with your method.  

Select the range that contains your categories for OverallData and name it OverallDataCategories.  Do the same for the other two sources and their categories by appending "Categories" to the original name.  You should have six names that are self explanatory.

OverallData
OverallDataCategories
RetireData
RetireDataCategories
InfrastructureData
InfrastructureDataCategories

Place this code in the worksheet that contains the chart.  If you are working with Office 97 let me know.  This will not work.  The button is redundant in this case though you are more than welcome to use a button if you must.  :)  The attached workbook demonstrates these instructions...  Have a nice day.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$J$24" Then
        With Me.ChartObjects(1).Chart
            .SetSourceData ThisWorkbook.Names(Target.Text).RefersToRange
            .SeriesCollection(1).XValues = ThisWorkbook.Names(Target.Text & "Categories").RefersToRange
        End With
    End If
End Sub

Select allOpen in new window

Edit-Return-Copy-of-Xl0000027-1-.xls

Thanks for this. It works. Can you please help me understand your code since I am trying to learn VBA. I have said what I understood from the code (See below). Can you answer my questions and clarify my understanding?

You mentioned that there are several ways of doing this. If there is another easy way, it would be great if you could post that code. This would help me learn (Only if you can!). You have earned your points already!

Clarify/Questions:

"With Me.ChartObjects(1).Chart"

-What does "Me" refer to? - Is it the application/ActiveSheet (Excel). So this would mean the
chart object in the active sheet?

If this is true, how do I refer to more charts on my worksheet? I might want to apply this code to
more than one chart on my worksheet. How do I refer to those charts?

".SetSourceData ThisWorkbook.Names(Target.Text).RefersToRange"

-This line is saying set the source data for the chart to whatever is in cell $j$24 (Named Range).
What does .ReferstoRange do?

".SeriesCollection(1).XValues = ThisWorkbook.Names(Target.Text & "Categories").RefersToRange"

-The purpose of this line is to get the category values to show up in the chart legend.
I dont understand what ".SeriesCollection(1).XValues" is doing in particular. Is this a property that
sets the category values for a chart? Also please clarify what RefersToRange is doing.

What do I need to do if the spread sheet has several charts that I want to use this code on?

-I noticed that you put all the code in the "Sheet" rather than use insert module. Is this because you wanted to use the "with Me" line? Will the code not work if I put everything in a new module?


- I am thinking of a scenario where I want to implement this functionality (change chart based on selection) for several different charts on my workbook but not based on the same action. There will be separate charts with different dropdowns/selection lists

- So to implement this, I would just need to change the "If Target.Address = "$J$24" Then" line to refer to my new target (Say $M$24) and create the relevant target named ranges.
Am I thinking correctly?. Also do I need to create a new sub for each chart and selection group?

- Me.ChartObjects("Chart1").Chart
"Chart1" - So if I have 4 different charts in a workbook and don't recall which chart was created first. How do I refer to them in code? I know chart series have a names but don't know where to look for the name like ("Chart1" "Chart2" etc)

Also, when should we write code within the sheet say "sheet1" and when should we insert a module and write code in that module.

Hi Darth.  To rename a chart, select a cell, press and hold the control key, and then select the embedded chart.  You will see its name in the name box.  If you wish to rename it, type a valid name in the name box and press enter.  As far as centralizing your code?  Yeah.  That would be the way to go but I cannot really help you unless I understand the scope of what you are doing.  I think the whole "me" issue is complicating things.  It's really just shorthand and I probably should not have used it in an example for someone who is unfamiliar with the language.  I'm pretty much out of time for the day but will show you an example procedure that can be called from anywhere in your workbook.  In any case, forget about me.  Not me, but the VBA me.  May the force be with you!  Adios.

"Also, when should we write code within the sheet say "sheet1" and when should we insert a module and write code in that module."

See this link to help you understand a bit more about scope.
http://www.cpearson.com/excel/Scope.aspx

Thanks man! Will wait for your procedure that can be called from anywhere in the workbook! Thanks a lot for helping me learn.