Dynsmic Charts

W

whitethomas12

Hi,

I am trying to create a dynamic chart that will update when I change a
Validation List (has list of names) . So far I am able to change the
validation list and have it activate a macro (right now just a test
macro that pops up a message box with the name I selected).

My goal is to have the validation list activate a macro that will go
to another sheet to select the relative dates and total times (A =
dates, C = times) and then update the chart.

I have tried to create a macro to just make a chart but it is
failing. the following is my code:

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = _
"=(Sheet4!A8,Sheet4!A15,Sheet4!A23)"
ActiveChart.SeriesCollection(1).Values = _
"=(Sheet4!$C$9,Sheet4!$C$16,Sheet4!$C$24)"
ActiveChart.SeriesCollection(1).Name = "=Sheet4!A2"

I am not sure how to get this running; even more to have it use a
different range. I figure that I should be able to create several
variables that are ranges and then add it to the above code, but I am
not sure how to get it to work

I appreciate all of your help
 
W

whitethomas12

I think you're trying to do what the combo box does in this example:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______












- Show quoted text -
Click to expand...

It is similar, but the reason why I need everything in a macro is
because everything in the excel macro is created by a macro that I
wrote. Now have I create another worksheet that will enable a
supervisor to view each of their staff's average working hours per pay
period on a chart. Right now thet can view everything without the
chart, but they would like a graphical view.

The chart selections will have to change each time when the macro ran
because some people may work more or less days and hours


Thank you
 
J

Jon Peltier

I just answered the other thread that had the ranges hard coded. To
construct one of those ranges, if you don't know it ahead of time, you need
to follow this kind of approach.


Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long

' example worksheet and ranges
Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A8,A15,A23")
Set rDataY = wsData.Range("C8,C15,C23")

' processing of X and Y addresses no matter how ranges were defined
For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & "'" & wsData.Name & "'!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) & ","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & "'" & wsData.Name & "'!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) & ","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


I think you're trying to do what the combo box does in this example:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______












- Show quoted text -
Click to expand...

It is similar, but the reason why I need everything in a macro is
because everything in the excel macro is created by a macro that I
wrote. Now have I create another worksheet that will enable a
supervisor to view each of their staff's average working hours per pay
period on a chart. Right now thet can view everything without the
chart, but they would like a graphical view.

The chart selections will have to change each time when the macro ran
because some people may work more or less days and hours


Thank you
 
W

whitethomas12

I just answered the other thread that had the ranges hard coded. To
construct one of those ranges, if you don't know it ahead of time, you need
to follow this kind of approach.

  Dim rDataX As Range
  Dim rDataY As Range
  Dim wsData As Worksheet
  Dim sAddressX As String
  Dim sAddressY As String
  Dim iArea As Long

  ' example worksheet and ranges
  Set wsData = Worksheets("Sheet4")
  Set rDataX = wsData.Range("A8,A15,A23")
  Set rDataY = wsData.Range("C8,C15,C23")

  ' processing of X and Y addresses no matter how ranges were defined
  For iArea = 1 To rDataX.Areas.Count
    sAddressX = sAddressX & "'" & wsData.Name & "'!"
    sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) & ","
  Next
  sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

  For iArea = 1 To rDataY.Areas.Count
    sAddressY = sAddressY & "'" & wsData.Name & "'!"
    sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) & ","
  Next
  sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

  ActiveChart.SeriesCollection(1).XValues = sAddressX
  ActiveChart.SeriesCollection(1).Values = sAddressY

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______






It is similar, but the reason why I need everything in a macro is
because everything in the excel macro is created by a macro that I
wrote.  Now have I create another worksheet that will enable a
supervisor to view each of their staff's average working hours per pay
period on a chart.  Right now thet can view everything without the
chart, but they would like a graphical view.

The chart selections will have to change each time when the macro ran
because some people may work more or less days and hours

Thank you- Hide quoted text -

- Show quoted text -
Click to expand...

Hmm, I just tried your example and it did not work for some odd
reason. The hard part about this project is that I created a macro
that basically copies and organizes time in and out from a data dump.
The sheet that contains the chart is my final piece. The chart
basically displays the employess average time in hours through a pay
period or a date range. All time in and out times are in one column.
When I create the chart manually, everything works out perfectly.

The following is my code combined with yours for the chart:


Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long


' example worksheet and ranges
Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A8,A15,A23")
Set rDataY = wsData.Range("C8,C15,C23")


' processing of X and Y addresses no matter how ranges were defined
For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & "'" & wsData.Name & "'!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)


For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & "'" & wsData.Name & "'!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY
ActiveChart.SeriesCollection(1).Name = "=Sheet4!A2"

I am not sure what I am doing wrong. I do know that each chart is
given a name from Excel (like Chart 1) and when you delete the chart
it is given another name like Chart 2. Could the code be failing
because I have been creating and deleting charts?

I definently appreciate your help.
 
J

Jon Peltier

I'll suggest again that you post your response on the top of the thread,
like most other users of this forum, so it's easier to follow.
I am not sure what I am doing wrong. I do know that each chart is
given a name from Excel (like Chart 1) and when you delete the chart
it is given another name like Chart 2. Could the code be failing
because I have been creating and deleting charts?
Click to expand...

Since you are using ActiveChart, the arbitrary name assigned by Excel should
not matter. After .SetSourceData, does your chart have any data actually
plotted? I tested my code several times while I worked out the strings
required for the addresses. Discontiguous ranges make this more problematic
than it should be.

Did you ever mention which version of Excel you were using?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


I just answered the other thread that had the ranges hard coded. To
construct one of those ranges, if you don't know it ahead of time, you
need
to follow this kind of approach.

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long

' example worksheet and ranges
Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A8,A15,A23")
Set rDataY = wsData.Range("C8,C15,C23")

' processing of X and Y addresses no matter how ranges were defined
For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & "'" & wsData.Name & "'!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) & ","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & "'" & wsData.Name & "'!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) & ","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______






It is similar, but the reason why I need everything in a macro is
because everything in the excel macro is created by a macro that I
wrote. Now have I create another worksheet that will enable a
supervisor to view each of their staff's average working hours per pay
period on a chart. Right now thet can view everything without the
chart, but they would like a graphical view.

The chart selections will have to change each time when the macro ran
because some people may work more or less days and hours

Thank you- Hide quoted text -

- Show quoted text -
Click to expand...

Hmm, I just tried your example and it did not work for some odd
reason. The hard part about this project is that I created a macro
that basically copies and organizes time in and out from a data dump.
The sheet that contains the chart is my final piece. The chart
basically displays the employess average time in hours through a pay
period or a date range. All time in and out times are in one column.
When I create the chart manually, everything works out perfectly.

The following is my code combined with yours for the chart:


Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long


' example worksheet and ranges
Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A8,A15,A23")
Set rDataY = wsData.Range("C8,C15,C23")


' processing of X and Y addresses no matter how ranges were defined
For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & "'" & wsData.Name & "'!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)


For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & "'" & wsData.Name & "'!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY
ActiveChart.SeriesCollection(1).Name = "=Sheet4!A2"

I am not sure what I am doing wrong. I do know that each chart is
given a name from Excel (like Chart 1) and when you delete the chart
it is given another name like Chart 2. Could the code be failing
because I have been creating and deleting charts?

I definently appreciate your help.
 
W

whitethomas12

I'll suggest again that you post your response on the top of the thread,
like most other users of this forum, so it's easier to follow.


Since you are using ActiveChart, the arbitrary name assigned by Excel should
not matter. After .SetSourceData, does your chart have any data actually
plotted? I tested my code several times while I worked out the strings
required for the addresses. Discontiguous ranges make this more problematic
than it should be.

Did you ever mention which version of Excel you were using?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______
















Hmm, I just tried your example and it did not work for some odd
reason.  The hard part about this project is that I created a macro
that basically copies and organizes time in and out from a data dump.
The sheet that contains the chart is my final piece. The chart
basically displays the employess average time in hours through a pay
period or a date range.  All time in and out times are in one column.
When I create the chart manually, everything works out perfectly.

The following is my code combined with yours for the chart:

  Dim rDataX As Range
  Dim rDataY As Range
  Dim wsData As Worksheet
  Dim sAddressX As String
  Dim sAddressY As String
  Dim iArea As Long

  ' example worksheet and ranges
  Set wsData = Worksheets("Sheet4")
  Set rDataX = wsData.Range("A8,A15,A23")
  Set rDataY = wsData.Range("C8,C15,C23")

  ' processing of X and Y addresses no matter how ranges were defined
  For iArea = 1 To rDataX.Areas.Count
    sAddressX = sAddressX & "'" & wsData.Name & "'!"
    sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
  Next
  sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

  For iArea = 1 To rDataY.Areas.Count
    sAddressY = sAddressY & "'" & wsData.Name & "'!"
    sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
  Next
  sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
    ActiveChart.SetSourceData
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
        PlotBy:=xlColumns
  ActiveChart.SeriesCollection(1).XValues = sAddressX
  ActiveChart.SeriesCollection(1).Values = sAddressY
  ActiveChart.SeriesCollection(1).Name = "=Sheet4!A2"

I am not sure what I am doing wrong.  I do know that each chart is
given a name from Excel (like Chart 1) and when you delete the chart
it is given another name like Chart 2.  Could the code be failing
because I have been creating and deleting charts?

I definently appreciate your help.- Hide quoted text -

- Show quoted text -
Click to expand...

OK, I got my macro to create the chart. All what need to do is
figure out how to update it now whithout having to recreate the chart
based on user selection.

The following is my current macro combined with your code example:

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long

Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A8,A15,A23")
Set rDataY = wsData.Range("C9,C16,C24")

For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & wsData.Name & "!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)


For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & wsData.Name & "!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

ActiveSheet.ChartObjects.Add(196.5, 39, 252.75, 162).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard _
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
Gallery:=xlLine, Format:=4, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="Working Hours", _
CategoryTitle:="", _
ValueTitle:="", ExtraTitle:=""
ActiveChart.SeriesCollection(1).XValues = sAddressX ' "=Sheet4!
R8C1,Sheet4!R15C1,Sheet4!R23C1"
ActiveChart.SeriesCollection(1).Values = sAddressY ' "=Sheet4!
R9C3,Sheet4!R16C3,Sheet4!R24C3"
ActiveChart.SeriesCollection(1).Name = "=Sheet4!R2C1"

If you or some can give me an example of how to update the chart
dynamically it would be a great help

Thank you for all of your help, I couldn't of done it with out your
support
 
W

whitethomas12

OK, I got my macro to create the chart.  All what need to  do is
figure out how to update it now whithout having to recreate the chart
based on user selection.

The following is my current macro combined with your code example:

 Dim rDataX As Range
  Dim rDataY As Range
  Dim wsData As Worksheet
  Dim sAddressX As String
  Dim sAddressY As String
  Dim iArea As Long

  Set wsData = Worksheets("Sheet4")
  Set rDataX = wsData.Range("A8,A15,A23")
  Set rDataY = wsData.Range("C9,C16,C24")

  For iArea = 1 To rDataX.Areas.Count
    sAddressX = sAddressX & wsData.Name & "!"
    sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
  Next
  sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

  For iArea = 1 To rDataY.Areas.Count
    sAddressY = sAddressY & wsData.Name & "!"
    sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
  Next
  sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

  ActiveSheet.ChartObjects.Add(196.5, 39, 252.75, 162).Select
  Application.CutCopyMode = False
  ActiveChart.ChartWizard _
      Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
      Gallery:=xlLine, Format:=4, PlotBy:=xlColumns, _
      CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
      Title:="Working Hours", _
      CategoryTitle:="", _
      ValueTitle:="", ExtraTitle:=""
  ActiveChart.SeriesCollection(1).XValues = sAddressX ' "=Sheet4!
R8C1,Sheet4!R15C1,Sheet4!R23C1"
  ActiveChart.SeriesCollection(1).Values = sAddressY ' "=Sheet4!
R9C3,Sheet4!R16C3,Sheet4!R24C3"
  ActiveChart.SeriesCollection(1).Name = "=Sheet4!R2C1"

If you or some can give me an example of how to update the chart
dynamically it would be a great help

Thank you for all of your help, I couldn't of done it with out your
support- Hide quoted text -

- Show quoted text -
Click to expand...

OK I figured out how to up the charts. The is a test macro that is
also testing my validation list box

Here is my code

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long
Dim slName As String
slName = "Baratt, Lisa A."




Range("C3").Select
If Not ActiveCell.Value = UCase(slName) Then
Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A38,A51")
Set rDataY = wsData.Range("C27,C29,C31,C32,C34,C36,C37")

For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & wsData.Name & "!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & wsData.Name & "!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

' the next string is important....you have to select the chart before
you delete or add to
' the series. I think this was my issue from the begining. If
Excel doesnt know
' which chart to select then it does make since that we should get
errors.


ActiveSheet.ChartObjects.Select
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY

End If

Thank you for all of your help. It was greatly appreciated. If you
know an easier way, please let me know
 
J

Jon Peltier

You don't need to select the chart first, if you reference it in another
way.

If it's the only chart on the worksheet, then the chart is

ActiveSheet.ChartObjects(1).Chart

Also, if you have one series in the chart, you don't need to delete it and
add a new series, just change the source.

So...

With ActiveSheet.ChartObjects(1).Chart
.SeriesCollection(1).XValues = sAddressX
.SeriesCollection(1).Values = sAddressY
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


OK, I got my macro to create the chart. All what need to do is
figure out how to update it now whithout having to recreate the chart
based on user selection.

The following is my current macro combined with your code example:

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long

Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A8,A15,A23")
Set rDataY = wsData.Range("C9,C16,C24")

For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & wsData.Name & "!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & wsData.Name & "!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

ActiveSheet.ChartObjects.Add(196.5, 39, 252.75, 162).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard _
Source:=Sheets("Sheet4").Range("A3:A23,C3:C23"), _
Gallery:=xlLine, Format:=4, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="Working Hours", _
CategoryTitle:="", _
ValueTitle:="", ExtraTitle:=""
ActiveChart.SeriesCollection(1).XValues = sAddressX ' "=Sheet4!
R8C1,Sheet4!R15C1,Sheet4!R23C1"
ActiveChart.SeriesCollection(1).Values = sAddressY ' "=Sheet4!
R9C3,Sheet4!R16C3,Sheet4!R24C3"
ActiveChart.SeriesCollection(1).Name = "=Sheet4!R2C1"

If you or some can give me an example of how to update the chart
dynamically it would be a great help

Thank you for all of your help, I couldn't of done it with out your
support- Hide quoted text -

- Show quoted text -
Click to expand...

OK I figured out how to up the charts. The is a test macro that is
also testing my validation list box

Here is my code

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long
Dim slName As String
slName = "Baratt, Lisa A."




Range("C3").Select
If Not ActiveCell.Value = UCase(slName) Then
Set wsData = Worksheets("Sheet4")
Set rDataX = wsData.Range("A38,A51")
Set rDataY = wsData.Range("C27,C29,C31,C32,C34,C36,C37")

For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & wsData.Name & "!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & wsData.Name & "!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

' the next string is important....you have to select the chart before
you delete or add to
' the series. I think this was my issue from the begining. If
Excel doesnt know
' which chart to select then it does make since that we should get
errors.


ActiveSheet.ChartObjects.Select
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY

End If

Thank you for all of your help. It was greatly appreciated. If you
know an easier way, please let me know
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Macro Help for Charts 8
Excel Macro for Charts 1
Code to Create Chart 0
Floating Chart? 2
Change event code Find .Activate does not activate Target.value 4
How to change the row number in macro code? 1
Loop to create charts 1
Chart Macro?!! Help Please 2

Top