Dynamic Chart with Multiple Series - VBA

[ncortez]

Hello all,

I have an excel sheet with two current inputs:

Baseline Cost
Target Cost

The user is supposed to enter these in, then click a button which will populate a table which is found below the chart being created (or filled... i have the chart pre-created to facilitate things). The table is fairly simple and looks like this:

The y labels are as follows:
Status ( formula is: Status = (Baseline Cost) + (Implemented Risks) - (Implemented Opportunities) )
Target Cost (this will never change... it's from the input)
Opportunity (Opportunity = (Current Status) - (Non implemented opportunities))
Risk (Risk = (Current Status) - (Non implemented risks))

The x labels are simply week 1, week 2 etc

What I'm struggling with is having the graph be updated when there's a change... so when a risk and/ or opportunity is implemented, the status would change along with either risk or opportunity.
Also, for the risk and opportunity, I only want it to only reflect the most current data point, as opposed to tracking it. So the range of these will only equal to a single cell.... the last one filled.

I have an xy scatter plot that currently gets filled with the Status and target cost, but I'm not sure how to get the graph to update only one or two series without having the entire chart be wiped out.

Could anyone please help me? I've looked on the internet but have yet to find something that has been applicable.

Here is what I have so far, hope this doesn't make things more confusing. The bolded section is what I'm struggling with.

Sub GenerateGraph_Click()


Dim ws As Worksheet
Dim ws_opprisk As Worksheet
Dim ws_delta As Worksheet


Dim Baseline As Double
Dim Target As Double
Dim Risk As Double
Dim Opp As Double
Dim Status As Long
Dim c As Long
Dim Delta As Double
Dim l As Long
Dim j As Long
Dim k As Long
Dim update_opp As Long
Dim update_risk As Long
Dim col As String
Dim rng As String
Dim Series As Integer




Set ws = Worksheets("StatusToTarget")
Set ws_opprisk = Worksheets("R&O")
Set ws_delta = Worksheets("R&O_Form")




'Get row with grand total for opportunity


ws_opprisk.Select
l = Application.WorksheetFunction.Match("RISK", Range("B1:B1500"), 0)
j = l - 1


'Get row with grand total for risk
 k = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 


Baseline = ws.Cells(4, 2).Value
Target = ws.Cells(6, 2).Value


    


ws.Select
   
'If graph is not yet populated at all
If ws.Cells(27, 2) = "" Then


    ws.Cells(27, 2).Value = Baseline
    ws.Range("B28:K28").Value = Target
    
    ws.Cells(30, 2).Value = ws_opprisk(k, 15)
     ws.Cells(29, 2).Value = ws_opprisk(j, 15)
    
    
'If initial baseline & target are in and graph is being updated
Else
    'Change caption of button
    If Me.cmdgraph.Caption = "Generate Graph" Then
        Me.cmdgraph.Caption = "Update Graph"
    End If


[B]    'Update opportunity $[/B]
[B]        ws.Cells(29, 1).Select[/B]
[B]        ActiveCell.End(xlToRight).Select[/B]
[B]        update_opp = ActiveCell.Column[/B]
[B]        'Check to see if current grand total is different than what is reflected in chart[/B]
[B]        If ws.Cells(29, update_opp).Value = ws_opprisk.Cells(j, 15) Then[/B]

[B]        Else[/B]
[B]        update_opp = update_opp + 1[/B]
[B]        ws.Cells(29, update_opp).Value = ws_opprisk.Cells(j, 15)[/B]
[B]        End If[/B]

[B]    'Update Risk $[/B]
[B]        ws.Cells(30, 1).Select[/B]
[B]        ActiveCell.End(xlToRight).Select[/B]
[B]        update_risk = ActiveCell.Column[/B]
[B]        'Check to see if current grand total is different than what is reflected in chart[/B]
[B]        If ws.Cells(30, update_risk).Value = ws_opprisk.Cells(k, 15) Then[/B]

[B]        Else[/B]
[B]        update_risk = update_risk + 1[/B]
[B]        ws.Cells(30, update_risk).Value = ws_opprisk.Cells(k, 15)[/B]
[B]        col = ColumnLetter(update_risk)[/B]
[B]        rng = col & "30"[/B]


[B]        'Adjust range of graph to reflect most recent opportunity and risk[/B]
[B]        With Charts(1)[/B]
[B]            .SeriesCollection(1).Values = ws.Range("B30:" & rng)[/B]
[B]            .SeriesCollection(1).Name = "Risk"[/B]
[B]            .Values = ws.Range(rng)[/B]
[B]        End With[/B]
[B]        'ws.ChartObjects("StatusToTarget").Activate[/B]
[B]        'ActiveChart.SeriesCollection("Risk").Select[/B]
[B]        'ActiveChart.SetSourceData Source:=ws.Range(rng)[/B]

        End If
        
'Change Status - needs to happen before range for risks & opps is changed since it's based on this!
ws.Cells(27, 1).Select
    ActiveCell.End(xlToRight).Select
    Status = ActiveCell.Value
    c = ActiveCell.Column
    c = c + 1


  NewStatus = Status + Delta


    ws.Cells(27, c) = NewStatus
      
           
End If




End Sub


Function ColumnLetter(col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function