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 Whe=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

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
_______


wrote in message
...
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 Whe=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

On Jan 16, 11:56*am, "Jon Peltier"
wrote:
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
_______

wrote in message

...



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 Whe=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- Hide quoted text -

- Show quoted text -

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

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
_______


wrote in message
...
On Jan 16, 11:56 am, "Jon Peltier"
wrote:
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
_______

wrote in message

...



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 Whe=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- Hide quoted text -

- Show quoted text -

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

On Jan 16, 7:58*pm, "Jon Peltier"
wrote:
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
_______

wrote in message

...
On Jan 16, 11:56 am, "Jon Peltier"
wrote:





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
_______

wrote in message

...

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 Whe=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- Hide quoted text -

- Show quoted text -

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 -

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 Whe=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.

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?

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
_______


wrote in message
...
On Jan 16, 7:58 pm, "Jon Peltier"
wrote:
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
_______

wrote in message

...
On Jan 16, 11:56 am, "Jon Peltier"
wrote:





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
_______

wrote in message

...

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 Whe=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- Hide quoted text -

- Show quoted text -

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 -

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 Whe=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.

On Jan 17, 8:19*am, "Jon Peltier"
wrote:
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?

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
_______

wrote in message

...
On Jan 16, 7:58 pm, "Jon Peltier"
wrote:





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
_______

wrote in message

...
On Jan 16, 11:56 am, "Jon Peltier"
wrote:

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
_______

wrote in message

....

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 Whe=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- Hide quoted text -

- Show quoted text -

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 -

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 Whe=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 -

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

On Jan 17, 5:12*pm, wrote:
On Jan 17, 8:19*am, "Jon Peltier"
wrote:





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?

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
_______

wrote in message

...
On Jan 16, 7:58 pm, "Jon Peltier"
wrote:

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
_______

wrote in message

....
On Jan 16, 11:56 am, "Jon Peltier"
wrote:

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
_______

wrote in message

...

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 Whe=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- Hide quoted text -

- Show quoted text -

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 -

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 Whe=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 -

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 -

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

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
_______


wrote in message
...
On Jan 17, 5:12 pm, wrote:
On Jan 17, 8:19 am, "Jon Peltier"
wrote:





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?

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
_______

wrote in message

...
On Jan 16, 7:58 pm, "Jon Peltier"
wrote:

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
_______

wrote in message

...
On Jan 16, 11:56 am, "Jon Peltier"
wrote:

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
_______

wrote in message

...

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 Whe=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- Hide quoted text -

- Show quoted text -

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 -

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 Whe=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 -

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 -

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