dynamic charts- Naming problem and ....

[ali]

Hi guys,

I am amazed by the expert cooperation and patronge I have found in
these forums. Hats off to our gurus.

I have a problem with dynamic charting. I am able to create a dynamic
chart using vba one time. But it doesn't get updated. When the macro
runs a new chart is created every time or I come across an application
error. I would like to have the following chart with name "mychart"
and I would like to keep updating it. Here is the code that I have
got.

Dim colcount As Integer
colcount = Cells(Rows.Count, 2).End(xlUp).Row
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("DesignGraph").Range(Cells(2, 2), Cells(colcount, 2)),
_
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject,
Name:="DesignGraph"
With Selection.Border
.Weight = 2
.LineStyle = -1
End With
Sheets("DesignGraph").DrawingObjects("Chart 53").RoundedCorners =
False
Sheets("DesignGraph").DrawingObjects("Chart 53").Shadow = False
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal,
Variant:=3, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 5
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal,
Variant:=1, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 39
End With

Thanks a lot.

ali

 

[Joel]

I got your macro to run without errors. It wasn't easy. Chartnames change
when they get created. Use some special code to solve the problem. Let me
know if you have any more questtions.


Sub test()


Dim colcount As Integer
Sheets("DesignGraph").Activate
RowCount = Cells(Rows.Count, 2).End(xlUp).Row
Set myrange = Worksheets("DesignGraph"). _
Range("B2:B" & RowCount)

Set mychart = Charts.Add
mychart.ChartType = xlLineMarkers
mychart.SetSourceData Source:=myrange
mychart.Location Where:=xlLocationAsObject, _
Name:="DesignGraph"
With Selection.Border
.Weight = 2
.LineStyle = -1
End With

chartname = "MyChart1"
Sheets("DesignGraph").Activate
chartname = ActiveChart.Name
For Each myshape In ActiveSheet.Shapes

'get chart - name changed
If InStr(chartname, myshape.Name) > 0 Then
newchartname = myshape.Name
End If
Next myshape

Sheets("DesignGraph").DrawingObjects(newchartname).RoundedCorners = False
Sheets("DesignGraph").DrawingObjects(newchartname).Shadow = False

Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
Variant:=3, Degree:=0.231372549019608

With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 5
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With

Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
Variant:=1, Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 39
End With

End Sub