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
"ali" wrote:
> 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
>
>
|
Similar Threads
