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