Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Help With Chart Object Problem

  Asked By: Ketan    Date: Jan 18    Category: MS Office    Views: 2777
  

This is my first time here. I am using Excel 2003 and am
self-taught in using VBA, and am still "green" in this area.

I used the macro recorder and modied some of the code in the hopes
that the VBA I wrote would work. Instead, it produces errors. I have a
Chart object on a Sheet that I wish to "feed" a range to chart. Since
the number of rows for the needed range changes from time to time, I
included a variable called amtrows. Then I try to "assign" a range to
variable XTemp. Then I try to feed that XTemp range into the Chart
object.

My modified code errors at this line --

Set XTemp = Range("C1").Resize(x, 0)

And at this line as well --

ActiveChart.SetSourceData Source:=Sheets("Sheet5").Range(XTemp),
PlotBy _ :=xlColumns

Could you please review my code below and tell me what to change ?
I would be most grateful for your help. Thank you and God bless you.

Dim amtrows As Long
Dim XTemp As Range

amtrows = Range("A8").Value

Set XTemp = Range("C1").Resize(x, 0)

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet5").Range(XTemp),
PlotBy _
:=xlColumns
ActiveWindow.Visible = False

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Sean Grant     Answered On: Jan 18

It won't like the zero in
Set XTemp = Range("C1").Resize(x, 0)
because you can't have a selection zero columns wide I think.
Also is x properly defined somewhere? I suspect you mean to use
amtrows there?

The line
ActiveChart.SetSourceData Source:=Sheets("Sheet5").Range(XTemp),
PlotBy _ :=xlColumns

will confuse vba  as you've already defined XTemp as a range so
Range(Xtemp) may be a little dodgy.
Set the range to include sheet info as well:
Set XTemp = Sheets("Sheet5").Range("C1").Resize(amtrows, 1)

and change the SetSource line to:
ActiveChart.SetSourceData Source:=XTemp, PlotBy:=xlColumns

It also helps to be explicit about which sheet Range("A8") is on:
amtrows = Sheets("Sheet5").Range("A8").Value


Macro in full:

Dim amtrows As Long
Dim XTemp As Range
amtrows = Sheets("Sheet5").Range("A8").Value
Set XTemp = Sheets("Sheet5").Range("C1").Resize(amtrows, 1)
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=XTemp, PlotBy:=xlColumns
ActiveWindow.Visible = False

 
Answer #2    Answered By: Huette Miller     Answered On: Jan 18

Since I posted my code and problem
earlier today, I have modified the code (see below). Despite my
modifications, I am still receiving errors.

I have a VBA coding problem  with the Sub below. All lines work fine
until I reach the lines commented with asterisks * * *. Those 7 lines
of Series Collection code are puzzling me. Could you please go over
my code and tell me (show me) what I'm doing wrong ?

Sub ChartTrial()

Dim DataRange As Range
Dim UpperLmtRange As Range
Dim LowerLmtRange As Range
Dim TimeFrame As Range
Dim DataName As String
Dim UpperLmtName As String
Dim LowerLmtName As String
Dim XMax As Long
Dim XMin As Long
Dim XUnit As Long
Dim amtrows As Long

'assign names
DataName = "Region1"
UpperLmtName = "Upper Limit"
LowerLmtName = "Lower Limit"

'determine axis scale limits
XMax = Range("Sheet3!B6").Value
XMin = Range("Sheet3!B7").Value
XUnit = Range("Sheet3!B8").Value

'get count of data rows to plot
amtrows = Range("Sheet5!C4").Value

'set-up ranges to plot
Set TimeRange = Range("Sheet3!C7").Resize(amtrows, 1)
Set DataRange = Range("Sheet3!D7").Resize(amtrows, 1)
Set UpperLmtRange = Range("Sheet3!E7").Resize(amtrows, 1)
Set LowerLmtRange = Range("Sheet3!F7").Resize(amtrows, 1)

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select

'set axis scale values
With ActiveChart.Axes(xlValue)
.MaximumScale = XMax
.MinimumScale = XMin
.MinorUnit = XUnit
End With

'assign data range
ActiveChart.SetSourceData Source:=DataRange, PlotBy:=xlColumns

ActiveChart.SeriesCollection(1).Name = DataName
ActiveChart.SeriesCollection(1).Values = DataRange

'* * *errors occur with the next 7 lines
ActiveChart.SeriesCollection(2).Name = UpperLmtName
ActiveChart.SeriesCollection(2).Values = UpperLmtRange
ActiveChart.SeriesCollection(3).Name = LowerLmtName
ActiveChart.SeriesCollection(3).Values = LowerLmtRange
ActiveChart.SeriesCollection(1).XValues = Range(TimeFrame)
ActiveChart.SeriesCollection(2).XValues = Range(TimeFrame)
ActiveChart.SeriesCollection(3).XValues = Range(TimeFrame)
'* * * errors end here

ActiveWindow.Visible = False

Range("A1").Select

End Sub

 
Answer #3    Answered By: Maria Miller     Answered On: Jan 18


1. Remove or comment out this line:
ActiveChart.SetSourceData Source:=DataRange, PlotBy:=xlColumns

Since you already have the chart  in place you don't need to redefine
and plot. Since DataRange is only one column wide you end up only
plotting one series, therefore later when you refer to series 2 and 3
etc. it balks. Just make sure you have 3 series and the chart type as
you like it before running the macro.

2. The line
Set TimeRange = Range("Sheet3!C7").Resize(amtrows, 1)
I believe should be:
Set TimeFrame = Range("Sheet3!C7").Resize(amtrows, 1)
since you have no Dim statement for TimeRange, but have one for
TimeFrame!

3. You've done the same as before with the three lines:
ActiveChart.SeriesCollection(1).XValues = Range(TimeFrame)
ActiveChart.SeriesCollection(2).XValues = Range(TimeFrame)
ActiveChart.SeriesCollection(3).XValues = Range(TimeFrame)

TimeFrame is already a range so amend to:
ActiveChart.SeriesCollection(1).XValues = TimeFrame
ActiveChart.SeriesCollection(2).XValues = TimeFrame
ActiveChart.SeriesCollection(3).XValues = TimeFrame

 
Didn't find what you were looking for? Find more on VBA Help With Chart Object Problem Or get search suggestion and latest updates.




Tagged: