1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel macro help for updating charts

Discussion in 'Business Applications' started by pctsvs, Jan 12, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. pctsvs

    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
     
  2. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    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?
     
  3. balloon_tom

    balloon_tom

    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
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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
     
  5. bomb #21

    bomb #21

    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:
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Pivot Charts a great idea! Just depending on the OP's data/structure. Nice call Andy!! :D
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/433400

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice