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.