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

darthvader747
darthvader747 used Ask the Experts™
on
Please see attached workbook- Sheet2 has all the data

This workbook has 4 Named Ranges:
Retire Data
InfrastructureData
OverallData
Chart Data = Overall Data

I use these named ranges to  make the chart change by inputting them in the variable  C_Type. This works fine.

But I need the categories to change as well. Right now even though the range changes ; the categores (See Legend) remains
$F5:$F$7. I need this to also dynamically update to $H5:4$H7 and
$M5:$M11 (Sheet 2 )

I want to enhance this code so that when  a chart is picked the categories also change therby I will have the correct legend

I am VBA newbie so please comment any code that you write. Code in this workbook:

Sub Change_Chart()
Dim C_Type As String
C_Type = Range("$J$24")

Names("ChartData").Value = "=" & C_Type
End Sub


Copy-of-Xl0000027.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Open in new window

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

Author

Commented:
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?

"You mentioned that there are several ways of doing this."

It really depends on your workbook.  If you have several charts that will be updated by one action, I might forego using named ranges and assign the ranges directly.  If, for example, you have three charts that will all be updated in like manner, you would end up with 18 named ranges.  Not a big deal but I would probably create a function to determine the ranges on the fly.  That's just my preference.

"With Me.ChartObjects(1).Chart"
The code is located within Sheet1.  Me is sort of like Java's This if you are familiar with Java.  If you used me in the workbook class, it would return a reference to the workbook.  A userform;  a reference to the userform.  See help.

With Sheet1.ChartObjects(1).Chart
With Worksheets("SummaryReport").ChartObjects(1).Chart
With Worksheets(1).ChartObjects(1).Chart

These are all synonomous as far as the resulting references.  To gain an understanding, just select any term and hit F1 for context sensative help.

ChartObjects(1).Chart returns a reference to the first chart that was added to the worksheet.

See the code below for refering to charts by name or by iteration...

See VBA help for ReferstoRange and SeriesCollection.  You will find a better explanation there.

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

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

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
-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)

Author

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial