Stuck with Chart Object

[parthi2929]

Hi
I am trying to create a chart object through VBA since my x-axis length is dynamic and I need to calculate the data series dynamically through code.
Before dwelling in to the calculation, I tried first to create a simple chart through VBA. Its just an empty chart and my aim is to have this chart updated for any change done in the inputs (inputs for computation of dataseries) in the worksheet.
The empty chart was created successfully but when I de-focus and activate the macro, the chart is created again. Butif I close the existing chart in worksheet, I need to create a new one (or atleast bring closed one with updated info). How do I do this?? Can any one please provide a sample how to do this?
Below is my unsuccessfull attempt:

Sub Plot_Chart()

'Get the current month and year
currentMonth = DatePart("m", Now)
currentYear = DatePart("yyyy", Now)


'draw a chart
If moneyChart Is Nothing Then
Set moneyChart = Charts.Add
Set moneyChart = moneyChart.Location(Where:=xlLocationAsObject, Name:="Monthly Consumption Analysis Chart 1")
'ElseIf reCreateChart = True Then
' Set moneyChart = Charts.Add
' 'Set moneyChart = ThisWorkbook.Worksheets("Monthly Consumption Analysis").ChartObjects("Monthly Consumption Analysis Chart").Chart
' Set moneyChart = moneyChart.Location(Where:=xlLocationAsObject, Name:="Monthly Consumption Analysis")
' reCreateChart = False
End If

Set mClsEvents = New clsChtEvts1
Set mClsEvents.pCht = moneyChart

MsgBox("Please help")

End Sub


Regards
Parthi2929

 

[doubledaffy]

What if, rather than having VBA create the chart, you just have it control the chart? This way you can just refer to the static chart handle (e.g. Chart1) and just dynamically edit the x-axis through the code. The steps when the macro would run would essentially be: clear old data series out (just to be safe), add new data series, point that data series to the range you want it to display, adjust any remaining details (axis length, color, labels). I've done this before and it works out pretty well.

 

[parthi2929]

The recalculation of dataseries needs automation - so I can simply write a macro to update the data series? (whose modification will any way modify the chart?)

That's what you mean?

 

[doubledaffy]

There are a couple of options to explore. Let's figure out what you're working with, because we might be able to skip VBA all together.

Do you have essentially a column for x-axis data and one for y-axis data, or is the chart more complicated?

Are you trying to make it so that the chart changes if you add more data? For example if you start with 10 entries and then add another 5 you want the chart to reflect this? Is that it?

Best,
~Doubledaffy

 

[parthi2929]

Hi
I have attached the excel file with sample data here:
http://www.gigasize.com/get/7thgoc1j7cc

(Is there a way we could directly upload and share the file instead of 3rd party uploading?)

The table A, is the main input where the user enters the EMI parameters.

The table B is the dataseries created. I then plot chart with cumulative values as shown in the chart.

Now everytime I change the number of years of EMI, I have to update teh chart for so many years (brown color values in table B).

Also if I want to see the plot for more than what is plotted say 10 years, then I have to create more values for each column (click and drag only but its still manual) manually... and then should update chart source data value ranges also.

So I wanted to create table B and the chart programmatically.
User has to enter details in table A and then just click the button "plot chart".

The macro then should create the chart directly (dataseries not needed)
That was my initial intention.

And then I got stuck when I start creating an empty chart as first step. I could not figure out logic for
1)to avoid creating multiple charts when already existing chart could be updated
2) only if user closed the existing chart,and then clicks "plot chart", a new chart should be created (but closing a chart does not result in "Nothing" for respective chart object making it difficult to detect this change)
3) If I use deactivate event to detect that closure mentioned in above step, then deactivate also happens when I de-focus (say, click on chart and click else where) fooling my macro to create another chart when "plot chart" is clicked next time.

Pl. help.

 

[doubledaffy]

I wrote up some code for you to use. A note before using it though...

The code is designed to work with exactly the format of the sheet that you gave me. If you change the layout of the sheet, you'll have to update the code to reflect the changes. The code is decently annotated so you can follow along and make changes where you see fit. Just add a button, link it to the macro, and you should be good to go. The macro is really only needed for when you add rows to the data, but it should give you a sense of how to work with a chart.

Sub btnGraphUpdate()

'Create Named Ranges that will point to the data series
StartRow = 16 'This is the starting row for all of the data in Table B
              'If the Worksheet layout changes this may need to be updated
Endrow = Cells(StartRow, 1).End(xlDown).Row
'------Named Range for the Month List-----
Xaxis = Range("A15").Value
ActiveWorkbook.Names.Add Name:=Xaxis, _
    RefersTo:=Range(Cells(StartRow, 1), Cells(Endrow, 1))
'------Named Range for the Pay1 List-----
Yaxis1 = Range("B3").Value & "_Cumulative"
ActiveWorkbook.Names.Add Name:=Yaxis1, _
    RefersTo:=Range(Cells(StartRow, 3), Cells(Endrow, 3))
'------Named Range for the Pay2 List-----
Yaxis2 = Range("C3").Value & "_Cumulative"
ActiveWorkbook.Names.Add Name:=Yaxis2, _
    RefersTo:=Range(Cells(StartRow, 5), Cells(Endrow, 5))

Sheets(1).ChartObjects("Chart 1").Activate  'Activate the chart to work with
    With ActiveChart  'Delete old series to avoid any leftovers or messes
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop
    End With
    With ActiveChart.SeriesCollection.NewSeries 'Create 1st data series
            .Name = Yaxis1
            .Values = Range(Yaxis1)
            .XValues = Range(Xaxis)
    End With
    With ActiveChart.SeriesCollection.NewSeries 'Create 2nd data series
            .Name = Yaxis2
            .Values = Range(Yaxis2)
            .XValues = Range(Xaxis)
    End With
'Give The Chart a Title
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = Range("A2")

'Format the X-axis
With ActiveChart.Axes(xlCategory, xlPrimary)
    .HasTitle = True
    .AxisTitle.Caption = Xaxis
    End With
'Format the Y-axis
With ActiveChart.Axes(xlValue, xlPrimary)
    .HasTitle = True
    .AxisTitle.Caption = "Pay in Dollars"
    .AxisTitle.Orientation = 90
    End With
End Sub

Enjoy

~DoubleDaffy

 

[gheyman]

I have been searching the net and reading a book to try and make a simply chart I have Dynamic. I need it to do exactly what you stated in your reply

"Are you trying to make it so that the chart changes if you add more data? For example if you start with 10 entries and then add another 5 you want the chart to reflect this? Is that it?

Best,
~Doubledaffy

Yes I just need it to adjust the number of columns in the chart. Sometimes the data in rows 2 and 3 are months out (2 to 3 columns to chart) or it could be 15 months

Sometimes from B2:E3 or it could be B2:Z3

Do yo think you can help?

Like I said two rows of data (which is Actual cost per month for two items)

So Column A2 is "Product1" and A3 is "Product2" Across Row 1 starting in Column B are the months. Columns My chart is Chart 4 on a tab named Cash Flow

Any help or even direction to a good source would be deeply appreciated. I have been researching for hours.