PDA

View Full Version : Chart multiple ranges from userform dropdown



MachaMacha
04-28-2008, 02:17 PM
I have a sheet1 with column a=dates and columns b through z as data corresponding to those dates. Row 1 has the name of each column. For example b1="close", c1="mid"

How can I create a dropdown user form to select just the ranges I want to create line charts for. For example I may only want the data from columns B, H, and E on the same line chart (not all the data from B:Z).

MachaMacha
04-28-2008, 02:29 PM
please see the 3 lines of comments


Sub Macro1()
Charts.Add
ActiveChart.ChartType = xlLineMarkers

'here i select columns B and D however I would like a form in Excel where I can choose the column
'also I am only selecting data from row 2 through 32 but would also like to have a form where I can
'control how many rows to plot

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B2:B32,D2:D32"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R32C1"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C2"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R2C1:R32C1"
ActiveChart.SeriesCollection(2).Name = "=Sheet1!R1C4"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub