Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need for categories to be displayed in chart
I two problems with this program below. Although it does print the data
correctly on each of eleven sheets, I now only need the program to print on sheets 2 and 9. Any idea how to change this FOr..Next staetment to add that flexibikity? The second problem I have is that the program doesn't display the data category names on the finished chart. (I have data that exist in 6 columns (x and five y variables) and the category names are listed directly above the five y variables). Unfortunately, the program only prints the Legend with: Series 1, 2, 3, 4, and 5. I tried to add the following lines to the program code below at the bottom but it crashed. With cho.Chart .SeriesCollection.Labels = True .SeriesCategory.Labels = True cho.Chart.ApplyDataLabels Type:=xlDataLabelsShow, LegendKey:=False End With How can I get the individual Category names to be displayed when I use the following VBA code: Sub OneChartPerSheet_v3() Dim ws As Worksheet Dim cho As ChartObject Dim sRange As String Dim dTop As Double Dim dLeft As Double Dim dHeight As Double Dim dWidth As Double Dim st As String ' change settings to suit sRange = "$A$6:$F$37" dTop = 45 dLeft = 460 dHeight = 350 dWidth = 320 For Each ws In ActiveWorkbook.Worksheets Set cho = ws.ChartObjects.Add(dLeft, dTop, dWidth, dHeight) 'set chart type cho.Chart.ChartType = xlXYScatter With cho.Chart .SetSourceData Source:=ws.Range(sRange), PlotBy:=xlColumns ' other chart formatting .HasTitle = True .ChartTitle.Characters.Text = ws.Range("A3") .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _ "FAM Fluorescence, RFUs" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "SR Fluorescence, RFUs" .HasLegend = True .Legend.Position = xlBottom End With Next End Sub ----------- Thanks for your time. I thought Excel macro programming would be easier than this since this has been more of a challenge than I anticipated. |
#2
|
|||
|
|||
Ted -
1. You could change your For Next to: For i = 2 to 9 step 7 Set ws = ActiveWorkbook.Worksheets(i) '' do the stuff Next 2. Your data range is "$A$6:$F$37". Delete the contents of cell A6, so column A is recognized as category labels and row 6 as series names. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ted Mifflin wrote: I two problems with this program below. Although it does print the data correctly on each of eleven sheets, I now only need the program to print on sheets 2 and 9. Any idea how to change this FOr..Next staetment to add that flexibikity? The second problem I have is that the program doesn't display the data category names on the finished chart. (I have data that exist in 6 columns (x and five y variables) and the category names are listed directly above the five y variables). Unfortunately, the program only prints the Legend with: Series 1, 2, 3, 4, and 5. I tried to add the following lines to the program code below at the bottom but it crashed. With cho.Chart .SeriesCollection.Labels = True .SeriesCategory.Labels = True cho.Chart.ApplyDataLabels Type:=xlDataLabelsShow, LegendKey:=False End With How can I get the individual Category names to be displayed when I use the following VBA code: Sub OneChartPerSheet_v3() Dim ws As Worksheet Dim cho As ChartObject Dim sRange As String Dim dTop As Double Dim dLeft As Double Dim dHeight As Double Dim dWidth As Double Dim st As String ' change settings to suit sRange = "$A$6:$F$37" dTop = 45 dLeft = 460 dHeight = 350 dWidth = 320 For Each ws In ActiveWorkbook.Worksheets Set cho = ws.ChartObjects.Add(dLeft, dTop, dWidth, dHeight) 'set chart type cho.Chart.ChartType = xlXYScatter With cho.Chart .SetSourceData Source:=ws.Range(sRange), PlotBy:=xlColumns ' other chart formatting .HasTitle = True .ChartTitle.Characters.Text = ws.Range("A3") .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _ "FAM Fluorescence, RFUs" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "SR Fluorescence, RFUs" .HasLegend = True .Legend.Position = xlBottom End With Next End Sub ----------- Thanks for your time. I thought Excel macro programming would be easier than this since this has been more of a challenge than I anticipated. |
#3
|
|||
|
|||
Jon:
Thanks for sending your solutions. Since I'm a VBA newbe, isn't there a need to add a Dim statement for the Worksheets variable? maybe something like: Dim Worksheet(i) As Object I also need to add a Dim statement for the counter i as well, right? Appreciate your help on this VBA program. Ted Jon Peltier wrote: Ted - 1. You could change your For Next to: For i = 2 to 9 step 7 Set ws = ActiveWorkbook.Worksheets(i) '' do the stuff Next 2. Your data range is "$A$6:$F$37". Delete the contents of cell A6, so column A is recognized as category labels and row 6 as series names. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ted Mifflin wrote: I two problems with this program below. Although it does print the data correctly on each of eleven sheets, I now only need the program to print on sheets 2 and 9. Any idea how to change this FOr..Next staetment to add that flexibikity? The second problem I have is that the program doesn't display the data category names on the finished chart. (I have data that exist in 6 columns (x and five y variables) and the category names are listed directly above the five y variables). Unfortunately, the program only prints the Legend with: Series 1, 2, 3, 4, and 5. I tried to add the following lines to the program code below at the bottom but it crashed. With cho.Chart .SeriesCollection.Labels = True .SeriesCategory.Labels = True cho.Chart.ApplyDataLabels Type:=xlDataLabelsShow, LegendKey:=False End With How can I get the individual Category names to be displayed when I use the following VBA code: Sub OneChartPerSheet_v3() Dim ws As Worksheet Dim cho As ChartObject Dim sRange As String Dim dTop As Double Dim dLeft As Double Dim dHeight As Double Dim dWidth As Double Dim st As String ' change settings to suit sRange = "$A$6:$F$37" dTop = 45 dLeft = 460 dHeight = 350 dWidth = 320 For Each ws In ActiveWorkbook.Worksheets Set cho = ws.ChartObjects.Add(dLeft, dTop, dWidth, dHeight) 'set chart type cho.Chart.ChartType = xlXYScatter With cho.Chart .SetSourceData Source:=ws.Range(sRange), PlotBy:=xlColumns ' other chart formatting .HasTitle = True .ChartTitle.Characters.Text = ws.Range("A3") .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _ "FAM Fluorescence, RFUs" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "SR Fluorescence, RFUs" .HasLegend = True .Legend.Position = xlBottom End With Next End Sub ----------- Thanks for your time. I thought Excel macro programming would be easier than this since this has been more of a challenge than I anticipated. |
#4
|
|||
|
|||
Ted -
Here's what you need to decla Dim ws as Worksheet Dim i as Integer ActiveWorkbook.Worksheets(i) is the collection of worksheets in the active workbook. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ted Mifflin wrote: Jon: Thanks for sending your solutions. Since I'm a VBA newbe, isn't there a need to add a Dim statement for the Worksheets variable? maybe something like: Dim Worksheet(i) As Object I also need to add a Dim statement for the counter i as well, right? Appreciate your help on this VBA program. Ted Jon Peltier wrote: Ted - 1. You could change your For Next to: For i = 2 to 9 step 7 Set ws = ActiveWorkbook.Worksheets(i) '' do the stuff Next 2. Your data range is "$A$6:$F$37". Delete the contents of cell A6, so column A is recognized as category labels and row 6 as series names. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ted Mifflin wrote: I two problems with this program below. Although it does print the data correctly on each of eleven sheets, I now only need the program to print on sheets 2 and 9. Any idea how to change this FOr..Next staetment to add that flexibikity? The second problem I have is that the program doesn't display the data category names on the finished chart. (I have data that exist in 6 columns (x and five y variables) and the category names are listed directly above the five y variables). Unfortunately, the program only prints the Legend with: Series 1, 2, 3, 4, and 5. I tried to add the following lines to the program code below at the bottom but it crashed. With cho.Chart .SeriesCollection.Labels = True .SeriesCategory.Labels = True cho.Chart.ApplyDataLabels Type:=xlDataLabelsShow, LegendKey:=False End With How can I get the individual Category names to be displayed when I use the following VBA code: Sub OneChartPerSheet_v3() Dim ws As Worksheet Dim cho As ChartObject Dim sRange As String Dim dTop As Double Dim dLeft As Double Dim dHeight As Double Dim dWidth As Double Dim st As String ' change settings to suit sRange = "$A$6:$F$37" dTop = 45 dLeft = 460 dHeight = 350 dWidth = 320 For Each ws In ActiveWorkbook.Worksheets Set cho = ws.ChartObjects.Add(dLeft, dTop, dWidth, dHeight) 'set chart type cho.Chart.ChartType = xlXYScatter With cho.Chart .SetSourceData Source:=ws.Range(sRange), PlotBy:=xlColumns ' other chart formatting .HasTitle = True .ChartTitle.Characters.Text = ws.Range("A3") .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _ "FAM Fluorescence, RFUs" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "SR Fluorescence, RFUs" .HasLegend = True .Legend.Position = xlBottom End With Next End Sub ----------- Thanks for your time. I thought Excel macro programming would be easier than this since this has been more of a challenge than I anticipated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Problem with xlusrgal.xls file | Charts and Charting in Excel | |||
Impedding/Overlaying Charts | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel |