+ Reply to Thread
Results 1 to 4 of 4

Need for categories to be displayed in chart

  1. #1
    Ted Mifflin
    Guest

    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. #2
    Jon Peltier
    Guest

    Re: Need for categories to be displayed in chart

    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. #3
    Ted Mifflin
    Guest

    Re: Need for categories to be displayed in chart

    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. #4
    Jon Peltier
    Guest

    Re: Need for categories to be displayed in chart

    Ted -

    Here's what you need to declare:

    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1