Excel macro help for updating charts

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

pctsvs

Thread Starter
Joined
Aug 15, 2005
Messages
74
I have a report that my company uses to track defects by supervisor and also by department. Currently I have these on two separate sheets. All the info is the same except for the charts, on one sheet the chart is for the sup and the other sheet is for the dept.

What I am wanting to know is how do I put both of these to one sheet using one chart? Example: If I want to view this by sup only the chart will have that info and if I want to view by dept only the chart will delete the sup info and put in the dept info.

Below is part of the code that creates the chart. I cannot upload my file as it is too big and even zipping it still too big.

This one is for the dept.

Code:
Dim DM_Department_CountB As Single
    DM_Department_CountB = Range("C3")
    Sheets("MONTHLY 8Ds").Select
    ActiveSheet.ChartObjects("Chart 3").Activate
    With ActiveChart.SeriesCollection(1)
        .Name = "='MONTHLY DATA BY DEPT'!R3C1:R" + Format(DM_Department_CountB + 2) + "C1"
        .Values = "='MONTHLY DATA BY DEPT'!R3C2:R" + Format(DM_Department_CountB + 2) + "C2"
    End With

This one is for sup.

Code:
Dim DM_Department_CountC As Single
    DM_Department_CountC = Range("C3")
    Sheets("MONTHLY 8Ds by sup").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveChart.SeriesCollection(1)
        .Name = "='MONTHLY DATA BY SUP'!R3C1:R" + Format(DM_Department_CountC + 2) + "C1"
        .Values = "='MONTHLY DATA BY SUP'!R3C2:R" + Format(DM_Department_CountC + 2) + "C2"
    End With
 

OBP

Joined
Mar 8, 2005
Messages
19,896
First of all can you say how the user will choose which Chart to see?
If it is by command buttons, why not put yor Chart VBA on the Command Buttons VBA?
 
Joined
Jan 4, 2006
Messages
107
I have recently completed a project that seems similar to yours. But keep in mind, I am VERY new at this and a sloppy code writer.
I have the 2 sets of data on 2 different work sheets, 1 named 'HIST' and the other 'SXP'. The cell values locations correspond exactly on the 2 sheets. I have a third sheet containing the graph. The following code assumes the graph is already displayed with one set of data. The value X determines which chart data I am looking at: 1=HIST, 2=SXP. The code below is a control button that switches between to two. Perhaps this will help get you started.
Tom

'A1 determines graph1 or graph2
If x=1 then x=2 else x=1
If x = 1 Then grf2 = "DEPT" Else grf2 = "SUP"

ActiveChart.SetSourceData Source:=Sheets(grf2).Range(gs(x)), PlotBy:= _
xlRows
 
Joined
Jul 25, 2004
Messages
5,458
Have you looked at the Object Browser in the VBE? (F2) This may help you in understanding charts a little better. Some sites which would be a big help I think are...

MS MVP Tushar Mehta:
http://www.tushar-mehta.com/

MS MVP Jon Peltier:
http://www.geocities.com/jonpeltier/

Check them out, they have great examples in each.

If I were to take a stab at your code, I would think (although honestly I did not test) something like this may work ...

Code:
    With Sheets("MONTHLY 8Ds").ChartObjects("Chart 3").SeriesCollection(1)
        .Name = "='MONTHLY DATA BY DEPT'!R3C1:R" + Format(DM_Department_CountB + 2) + "C1"
        .Values = "='MONTHLY DATA BY DEPT'!R3C2:R" + Format(DM_Department_CountB + 2) + "C2"
    End With

    With Sheets("MONTHLY 8Ds by sup").ChartObjects("Chart 1").SeriesCollection(1)
        .Name = "='MONTHLY DATA BY SUP'!R3C1:R" + Format(DM_Department_CountC + 2) + "C1"
        .Values = "='MONTHLY DATA BY SUP'!R3C2:R" + Format(DM_Department_CountC + 2) + "C2"
    End With
 
Joined
Jul 1, 2005
Messages
8,546
Hey Zack, I sat this one out, can't stand charts.

But since you're here, might this be a job in any shape or form for a Pivot Chart* (with a page field for sup/dept.)?

* :eek:
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top