Charts switch from 'Series in Rows' to 'Series in Columns'

I use VBA to create charts in Excel 2003, but find that sometimes the
Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not
intended), even if I have specified 'Series in Rows'.  This happens
intermittently, and I am not sure what I am doing wrong.  I do save
the workbook as Microsoft Excel 97 so that a user with Excel 2000 or
Excel 2003 can use the workbook.

Thank you for any suggestions.

0
3/21/2007 3:00:43 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
688 Views

Similar Articles

[PageSpeed] 52

Hard to tell if you keep the code secret.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peace" <overlandparkjoe@yahoo.com> wrote in message 
news:1174489243.640244.152530@n76g2000hsh.googlegroups.com...
>I use VBA to create charts in Excel 2003, but find that sometimes the
> Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not
> intended), even if I have specified 'Series in Rows'.  This happens
> intermittently, and I am not sure what I am doing wrong.  I do save
> the workbook as Microsoft Excel 97 so that a user with Excel 2000 or
> Excel 2003 can use the workbook.
>
> Thank you for any suggestions.
> 


0
jonxlmvpNO (4558)
3/21/2007 8:01:44 PM
Hello Jon,  Appreciate your response.  I wondered if anyone had had a
similar issue for which a solution already exists.  I did not post the
code as it is very lengthy, and in different modules.  I am hopefully
reproducing the relevant portion of it.  I would be happy to share the
workbook if that would help.  Thanks.

The code is designed to chart data by column, or by row, depending on
the position of the active cell in the table underlying the chart.  If
the activecell is other than row 15,  and is in column 1, then the row
provides data points for the chart.  If the active cell is in row 15
and other than column 1, then that column provides the data points.
If the active cell is elsewhere, it is forced to 'come' to row 15,
column 1 till the user makes a selection.

Sub UpdateChart()

On Error Resume Next
Set TheChartObj = ActiveSheet.ChartObjects(1)
Set TheChart = TheChartObj.Chart
UserRow = ActiveCell.Row
UserCol = ActiveCell.Column

krows = _
Application.WorksheetFunction.CountA(ActiveSheet. _
Range("A:A").SpecialCells(xlCellTypeVisible))
kcols = _
Application.WorksheetFunction.CountA(ActiveSheet. _
Rows("15:15").SpecialCells(xlCellTypeVisible))

'======================================================================
'clear shading of rows
Range(Cells(16, 2), Cells(100, 100)).Interior.ColorIndex = xlNone
'======================================================================

'code to shift from row to columns
If UserCol > 1 And UserCol < kcols + 1 And UserRow = 15 Then
'for vertical
    Set SrcRange = ActiveSheet.Range(Cells(15, UserCol), Cells(15 +
krows - 1, UserCol))
    PlotBy = xlColumns
ElseIf UserRow > 15 And UserRow < (15 + krows) And UserCol = 1 Then
'for horizontal
    Set SrcRange = ActiveSheet.Range(Cells(UserRow, StartCol),
Cells(UserRow, EndCol))
    Set SrcRange1 = ActiveSheet.Range(Cells(25, StartCol), Cells(25,
StartCol + EndCol))
    PlotBy = xlRows
Else
Range("A15").Select
    PlotBy = 0
End If
'======================================================
Select Case PlotBy

Case 1              ' by xlColumns
SrcRange.Interior.ColorIndex = 7
TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
UserRow & "C1"
TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy
TheChart.SeriesCollection.NewSeries
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"

        With TheChart
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
    End With

'set label for each series
Set xlblrng = ActiveSheet.Range(Cells(15, StartCol), Cells(15,
StartCol + EndCol - 2))
For Each srs In TheChart.SeriesCollection
    srs.XValues = xlblrng
Next
TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B77") &
vbCr & ActiveSheet.Range("B78")
'----------------------------------------
Case 2              ' by xlrows
SrcRange.Interior.ColorIndex = 4
TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy

'set label for each series
Set xlblrng = ActiveSheet.Range(Cells(16, 1), Cells(16 + krows, 1))
For Each srs In TheChart.SeriesCollection
On Error Resume Next
    srs.XValues = xlblrng
Next
TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
vbCr & ActiveSheet.Range("B76")

End Select
'======================================================
Select Case ActiveSheet.Name
Case Is = "Region_Year_o_Year"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"

        With TheChart
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
    End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
    If PlotBy = 1 Then
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1    ' horizontal = timeseries
        kChartType = 51
        kMajorUnitScale = xlYears
        kMinorUnitScale = xlYears
        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
    Else
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1 ' vertical = cross section
        kChartType = 51
        kMajorUnitScale = xlYears
        kMinorUnitScale = xlYears
        ChartTitleText = "HPA Cross-Section for Year " &
Format(Cells(15, UserCol).Value, "yyyy")
    End If

TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
vbCr & ActiveSheet.Range("B76")

Case Is = "State_Year_o_Year"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"

        With TheChart
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
    End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
    If PlotBy = 1 Then
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1    ' horizontal = timeseries
        kChartType = 51
        kMajorUnitScale = xlYears
        kMinorUnitScale = xlYears
        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
    Else
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1 ' vertical = cross section
        kChartType = 51
        kMajorUnitScale = xlYears
        kMinorUnitScale = xlYears
        ChartTitleText = "HPA Cross-Section for Year " _
                & Format(Cells(15, UserCol).Value, "yyyy")
    End If

Case Is = "Region_Qrtr_o_Qrtr"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"

        With TheChart
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
    End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
    If PlotBy = 1 Then
        kMajorUnit = 3
        kMinorUnit = 3
        TkLblSpc = 1    ' horizontal = timeseries
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
    Else
        kMajorUnit = 3
        kMinorUnit = 3
        TkLblSpc = 1 ' vertical = cross section
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Cross-Section for Quarter ending " _
                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
    End If

Case Is = "State_Qrtr_o_Qrtr"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"

        With TheChart
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
    End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
    If PlotBy = 1 Then
        kMajorUnit = 3
        kMinorUnit = 3
        TkLblSpc = 1    ' horizontal = timeseries
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
    Else
        kMajorUnit = 3
        kMinorUnit = 3
        TkLblSpc = 1 ' vertical = cross section
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Cross-Section for Quarter ending " _
                & Format(Cells(15, UserCol).Value, "mmm-yyyy")

    End If

Case Is = "Region_Month_o_Month"
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"

        With TheChart
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
    End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
    If PlotBy = 1 Then
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1  ' horizontal = timeseries
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
    Else
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1 ' vertical = cross section
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Cross-Section for Month " _
                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
    End If

Case Is = "State_Month_o_Month"
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"

        With TheChart
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
    End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
    If PlotBy = 1 Then
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1    ' horizontal = timeseries
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
    Else
        kMajorUnit = 1
        kMinorUnit = 1
        TkLblSpc = 1 ' vertical = cross section
        kChartType = 51
        kMajorUnitScale = xlMonths
        kMinorUnitScale = xlMonths
        ChartTitleText = "HPA Cross-Section for Month " _
                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
    End If
End Select

'==========================================
Select Case PlotBy

Case 1              ' by xlColumns, timeseries, horizontal
    With ActiveSheet.ChartObjects
    '    .Top = Range(Cells(2, StartCol), Cells(2, StartCol)).Top
        .Left = Range(Cells(2, StartCol), Cells(2, StartCol)).Left
    End With
        ActiveWindow.ScrollColumn = StartCol

Case 2              ' by xlrows = vertical
With ActiveSheet.ChartObjects
'    .Top = Range(Cells(2, ActiveCell.Column), Cells(2,
ActiveCell.Column)).Top
    .Left = Range(Cells(15, ActiveCell.Column), Cells(15,
ActiveCell.Column)).Left
End With
    ActiveWindow.ScrollColumn = ActiveCell.Column
End Select

    With TheChart.Axes(xlCategory)
        .CrossesAt = 1
        .TickLabelSpacing = 1
        .TickMarkSpacing = 1
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .BaseUnitIsAuto = True
        .MajorUnit = kMajorUnit
        .MajorUnitScale = kMajorUnitScale
        .MinorUnit = kMinorUnit
        .MinorUnitScale = kMinorUnitScale
        .Crosses = xlCustom
        .AxisBetweenCategories = False
        .ReversePlotOrder = False

        .MajorTickMark = xlOutside
        .MinorTickMark = xlNone
        .TickLabelPosition = xlLow
    End With
    TheChart.ChartTitle.Text = ChartTitleText
    TheChart.Activate
    ActiveChart.Axes(xlCategory).Select
    'With Selection.TickLabels
        '.Alignment = xlCenter
        '.Offset = 100
        '.ReadingOrder = xlContext
        '.Orientation = 90
    'End With

'TheChartObj.Chart.ChartType = 51
TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
UserRow & "C1"
TheChart.Refresh
TheChart.Axes(xlCategory).TickLabelPosition = xlLow
TheChartObj.Visible = True
Application.ScreenUpdating = True
End Sub


On Mar 21, 4:01 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Hard to tell if you keep the code secret.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutionshttp://PeltierTech.com
> _______
>
> "Peace" <overlandpark...@yahoo.com> wrote in message
>
> news:1174489243.640244.152530@n76g2000hsh.googlegroups.com...
>
>
>
> >I use VBA to create charts in Excel 2003, but find that sometimes the
> > Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not
> > intended), even if I have specified 'Series in Rows'.  This happens
> > intermittently, and I am not sure what I am doing wrong.  I do save
> > the workbook as Microsoft Excel 97 so that a user with Excel 2000 or
> > Excel 2003 can use the workbook.
>
> > Thank you for any suggestions.- Hide quoted text -
>
> - Show quoted text -


0
3/21/2007 9:09:46 PM
What are typical values for krows and kcols?

At first glance I notice that in the Select Case PlotBy, under Case 
xlColumns, you have values defined by

    "=" & ActiveSheet.Name & "!R25C" & StartCol & ":R25C" & StartCol + 
EndCol - 2

which means it is a range one row high by several columns wide (the same 
type of values definition appears in several places in the worksheet name 
select case). This is the definition of "by row", isn't it?

You should do the SetSourceData before defining SeriesCollection(1).Name.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peace" <overlandparkjoe@yahoo.com> wrote in message 
news:1174511386.574488.266830@o5g2000hsb.googlegroups.com...
> Hello Jon,  Appreciate your response.  I wondered if anyone had had a
> similar issue for which a solution already exists.  I did not post the
> code as it is very lengthy, and in different modules.  I am hopefully
> reproducing the relevant portion of it.  I would be happy to share the
> workbook if that would help.  Thanks.
>
> The code is designed to chart data by column, or by row, depending on
> the position of the active cell in the table underlying the chart.  If
> the activecell is other than row 15,  and is in column 1, then the row
> provides data points for the chart.  If the active cell is in row 15
> and other than column 1, then that column provides the data points.
> If the active cell is elsewhere, it is forced to 'come' to row 15,
> column 1 till the user makes a selection.
>
> Sub UpdateChart()
>
> On Error Resume Next
> Set TheChartObj = ActiveSheet.ChartObjects(1)
> Set TheChart = TheChartObj.Chart
> UserRow = ActiveCell.Row
> UserCol = ActiveCell.Column
>
> krows = _
> Application.WorksheetFunction.CountA(ActiveSheet. _
> Range("A:A").SpecialCells(xlCellTypeVisible))
> kcols = _
> Application.WorksheetFunction.CountA(ActiveSheet. _
> Rows("15:15").SpecialCells(xlCellTypeVisible))
>
> '======================================================================
> 'clear shading of rows
> Range(Cells(16, 2), Cells(100, 100)).Interior.ColorIndex = xlNone
> '======================================================================
>
> 'code to shift from row to columns
> If UserCol > 1 And UserCol < kcols + 1 And UserRow = 15 Then
> 'for vertical
>    Set SrcRange = ActiveSheet.Range(Cells(15, UserCol), Cells(15 +
> krows - 1, UserCol))
>    PlotBy = xlColumns
> ElseIf UserRow > 15 And UserRow < (15 + krows) And UserCol = 1 Then
> 'for horizontal
>    Set SrcRange = ActiveSheet.Range(Cells(UserRow, StartCol),
> Cells(UserRow, EndCol))
>    Set SrcRange1 = ActiveSheet.Range(Cells(25, StartCol), Cells(25,
> StartCol + EndCol))
>    PlotBy = xlRows
> Else
> Range("A15").Select
>    PlotBy = 0
> End If
> '======================================================
> Select Case PlotBy
>
> Case 1              ' by xlColumns
> SrcRange.Interior.ColorIndex = 7
> TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
> UserRow & "C1"
> TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy
> TheChart.SeriesCollection.NewSeries
> TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
> TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
> & StartCol & ":R25C" & StartCol + EndCol - 2
> TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>        "Line - Column on 2 Axes"
>
>        With TheChart
>        .Axes(xlValue, xlSecondary).HasTitle = True
>        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "United States"
>    End With
>
> 'set label for each series
> Set xlblrng = ActiveSheet.Range(Cells(15, StartCol), Cells(15,
> StartCol + EndCol - 2))
> For Each srs In TheChart.SeriesCollection
>    srs.XValues = xlblrng
> Next
> TheChart.HasTitle = True
> TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B77") &
> vbCr & ActiveSheet.Range("B78")
> '----------------------------------------
> Case 2              ' by xlrows
> SrcRange.Interior.ColorIndex = 4
> TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy
>
> 'set label for each series
> Set xlblrng = ActiveSheet.Range(Cells(16, 1), Cells(16 + krows, 1))
> For Each srs In TheChart.SeriesCollection
> On Error Resume Next
>    srs.XValues = xlblrng
> Next
> TheChart.HasTitle = True
> TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
> vbCr & ActiveSheet.Range("B76")
>
> End Select
> '======================================================
> Select Case ActiveSheet.Name
> Case Is = "Region_Year_o_Year"
>
> TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
> TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
> & StartCol & ":R25C" & StartCol + EndCol - 2
> TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>        "Line - Column on 2 Axes"
>
>        With TheChart
>        .Axes(xlValue, xlSecondary).HasTitle = True
>        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "United States"
>    End With
>
> ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
> ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
>    If PlotBy = 1 Then
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1    ' horizontal = timeseries
>        kChartType = 51
>        kMajorUnitScale = xlYears
>        kMinorUnitScale = xlYears
>        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
> 1).Value
>    Else
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1 ' vertical = cross section
>        kChartType = 51
>        kMajorUnitScale = xlYears
>        kMinorUnitScale = xlYears
>        ChartTitleText = "HPA Cross-Section for Year " &
> Format(Cells(15, UserCol).Value, "yyyy")
>    End If
>
> TheChart.HasTitle = True
> TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
> vbCr & ActiveSheet.Range("B76")
>
> Case Is = "State_Year_o_Year"
>
> TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
> TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
> & StartCol & ":R67C" & StartCol + EndCol - 2
> TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>        "Line - Column on 2 Axes"
>
>        With TheChart
>        .Axes(xlValue, xlSecondary).HasTitle = True
>        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "United States"
>    End With
>
> ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
> ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
>    If PlotBy = 1 Then
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1    ' horizontal = timeseries
>        kChartType = 51
>        kMajorUnitScale = xlYears
>        kMinorUnitScale = xlYears
>        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
> 1).Value
>    Else
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1 ' vertical = cross section
>        kChartType = 51
>        kMajorUnitScale = xlYears
>        kMinorUnitScale = xlYears
>        ChartTitleText = "HPA Cross-Section for Year " _
>                & Format(Cells(15, UserCol).Value, "yyyy")
>    End If
>
> Case Is = "Region_Qrtr_o_Qrtr"
>
> TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
> TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
> & StartCol & ":R25C" & StartCol + EndCol - 2
> TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>        "Line - Column on 2 Axes"
>
>        With TheChart
>        .Axes(xlValue, xlSecondary).HasTitle = True
>        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "United States"
>    End With
>
> ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
> 15
> ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
>    If PlotBy = 1 Then
>        kMajorUnit = 3
>        kMinorUnit = 3
>        TkLblSpc = 1    ' horizontal = timeseries
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
> 1).Value
>    Else
>        kMajorUnit = 3
>        kMinorUnit = 3
>        TkLblSpc = 1 ' vertical = cross section
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Cross-Section for Quarter ending " _
>                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
>    End If
>
> Case Is = "State_Qrtr_o_Qrtr"
>
> TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
> TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
> & StartCol & ":R67C" & StartCol + EndCol - 2
> TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>        "Line - Column on 2 Axes"
>
>        With TheChart
>        .Axes(xlValue, xlSecondary).HasTitle = True
>        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "United States"
>    End With
>
> ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
> 15
> ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
>    If PlotBy = 1 Then
>        kMajorUnit = 3
>        kMinorUnit = 3
>        TkLblSpc = 1    ' horizontal = timeseries
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
> 1).Value
>    Else
>        kMajorUnit = 3
>        kMinorUnit = 3
>        TkLblSpc = 1 ' vertical = cross section
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Cross-Section for Quarter ending " _
>                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
>
>    End If
>
> Case Is = "Region_Month_o_Month"
> TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
> TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
> & StartCol & ":R25C" & StartCol + EndCol - 2
> TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>        "Line - Column on 2 Axes"
>
>        With TheChart
>        .Axes(xlValue, xlSecondary).HasTitle = True
>        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "United States"
>    End With
>
> ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
> 15
> ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
>    If PlotBy = 1 Then
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1  ' horizontal = timeseries
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
> 1).Value
>    Else
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1 ' vertical = cross section
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Cross-Section for Month " _
>                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
>    End If
>
> Case Is = "State_Month_o_Month"
> TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
> TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
> & StartCol & ":R67C" & StartCol + EndCol - 2
> TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>        "Line - Column on 2 Axes"
>
>        With TheChart
>        .Axes(xlValue, xlSecondary).HasTitle = True
>        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "United States"
>    End With
>
> ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
> 15
> ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
>    If PlotBy = 1 Then
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1    ' horizontal = timeseries
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
> 1).Value
>    Else
>        kMajorUnit = 1
>        kMinorUnit = 1
>        TkLblSpc = 1 ' vertical = cross section
>        kChartType = 51
>        kMajorUnitScale = xlMonths
>        kMinorUnitScale = xlMonths
>        ChartTitleText = "HPA Cross-Section for Month " _
>                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
>    End If
> End Select
>
> '==========================================
> Select Case PlotBy
>
> Case 1              ' by xlColumns, timeseries, horizontal
>    With ActiveSheet.ChartObjects
>    '    .Top = Range(Cells(2, StartCol), Cells(2, StartCol)).Top
>        .Left = Range(Cells(2, StartCol), Cells(2, StartCol)).Left
>    End With
>        ActiveWindow.ScrollColumn = StartCol
>
> Case 2              ' by xlrows = vertical
> With ActiveSheet.ChartObjects
> '    .Top = Range(Cells(2, ActiveCell.Column), Cells(2,
> ActiveCell.Column)).Top
>    .Left = Range(Cells(15, ActiveCell.Column), Cells(15,
> ActiveCell.Column)).Left
> End With
>    ActiveWindow.ScrollColumn = ActiveCell.Column
> End Select
>
>    With TheChart.Axes(xlCategory)
>        .CrossesAt = 1
>        .TickLabelSpacing = 1
>        .TickMarkSpacing = 1
>        .MinimumScaleIsAuto = True
>        .MaximumScaleIsAuto = True
>        .BaseUnitIsAuto = True
>        .MajorUnit = kMajorUnit
>        .MajorUnitScale = kMajorUnitScale
>        .MinorUnit = kMinorUnit
>        .MinorUnitScale = kMinorUnitScale
>        .Crosses = xlCustom
>        .AxisBetweenCategories = False
>        .ReversePlotOrder = False
>
>        .MajorTickMark = xlOutside
>        .MinorTickMark = xlNone
>        .TickLabelPosition = xlLow
>    End With
>    TheChart.ChartTitle.Text = ChartTitleText
>    TheChart.Activate
>    ActiveChart.Axes(xlCategory).Select
>    'With Selection.TickLabels
>        '.Alignment = xlCenter
>        '.Offset = 100
>        '.ReadingOrder = xlContext
>        '.Orientation = 90
>    'End With
>
> 'TheChartObj.Chart.ChartType = 51
> TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
> UserRow & "C1"
> TheChart.Refresh
> TheChart.Axes(xlCategory).TickLabelPosition = xlLow
> TheChartObj.Visible = True
> Application.ScreenUpdating = True
> End Sub
>
>
> On Mar 21, 4:01 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>> Hard to tell if you keep the code secret.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutionshttp://PeltierTech.com
>> _______
>>
>> "Peace" <overlandpark...@yahoo.com> wrote in message
>>
>> news:1174489243.640244.152530@n76g2000hsh.googlegroups.com...
>>
>>
>>
>> >I use VBA to create charts in Excel 2003, but find that sometimes the
>> > Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not
>> > intended), even if I have specified 'Series in Rows'.  This happens
>> > intermittently, and I am not sure what I am doing wrong.  I do save
>> > the workbook as Microsoft Excel 97 so that a user with Excel 2000 or
>> > Excel 2003 can use the workbook.
>>
>> > Thank you for any suggestions.- Hide quoted text -
>>
>> - Show quoted text -
>
> 


0
jonxlmvpNO (4558)
3/21/2007 11:50:09 PM
Thanks, Jon, for your suggestions.
krows take on values from 9 to 52.
kcols take on values from 1 to 84

I will check out your suggestion to SetSourceData before defining
SeriesCollection(1).Name and also examine the code for possible
errors.

Have a good day.



On Mar 21, 7:50 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> What are typical values for krows and kcols?
>
> At first glance I notice that in the Select Case PlotBy, under Case
> xlColumns, you have values defined by
>
>     "=3D" & ActiveSheet.Name & "!R25C" & StartCol & ":R25C" & StartCol +
> EndCol - 2
>
> which means it is a range one row high by several columns wide (the same
> type of values definition appears in several places in the worksheet name
> select case). This is the definition of "by row", isn't it?
>
> You should do the SetSourceData before defining SeriesCollection(1).Name.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutionshttp://PeltierTech.com
> _______
>
> "Peace" <overlandpark...@yahoo.com> wrote in message
>
> news:1174511386.574488.266830@o5g2000hsb.googlegroups.com...
>
>
>
> > Hello Jon,  Appreciate your response.  I wondered if anyone had had a
> > similar issue for which a solution already exists.  I did not post the
> > code as it is very lengthy, and in different modules.  I am hopefully
> > reproducing the relevant portion of it.  I would be happy to share the
> > workbook if that would help.  Thanks.
>
> > The code is designed to chart data by column, or by row, depending on
> > the position of the active cell in the table underlying the chart.  If
> > the activecell is other than row 15,  and is in column 1, then the row
> > provides data points for the chart.  If the active cell is in row 15
> > and other than column 1, then that column provides the data points.
> > If the active cell is elsewhere, it is forced to 'come' to row 15,
> > column 1 till the user makes a selection.
>
> > Sub UpdateChart()
>
> > On Error Resume Next
> > Set TheChartObj =3D ActiveSheet.ChartObjects(1)
> > Set TheChart =3D TheChartObj.Chart
> > UserRow =3D ActiveCell.Row
> > UserCol =3D ActiveCell.Column
>
> > krows =3D _
> > Application.WorksheetFunction.CountA(ActiveSheet. _
> > Range("A:A").SpecialCells(xlCellTypeVisible))
> > kcols =3D _
> > Application.WorksheetFunction.CountA(ActiveSheet. _
> > Rows("15:15").SpecialCells(xlCellTypeVisible))
>
> > '=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > 'clear shading of rows
> > Range(Cells(16, 2), Cells(100, 100)).Interior.ColorIndex =3D xlNone
> > '=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> > 'code to shift from row to columns
> > If UserCol > 1 And UserCol < kcols + 1 And UserRow =3D 15 Then
> > 'for vertical
> >    Set SrcRange =3D ActiveSheet.Range(Cells(15, UserCol), Cells(15 +
> > krows - 1, UserCol))
> >    PlotBy =3D xlColumns
> > ElseIf UserRow > 15 And UserRow < (15 + krows) And UserCol =3D 1 Then
> > 'for horizontal
> >    Set SrcRange =3D ActiveSheet.Range(Cells(UserRow, StartCol),
> > Cells(UserRow, EndCol))
> >    Set SrcRange1 =3D ActiveSheet.Range(Cells(25, StartCol), Cells(25,
> > StartCol + EndCol))
> >    PlotBy =3D xlRows
> > Else
> > Range("A15").Select
> >    PlotBy =3D 0
> > End If
> > '=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
> > Select Case PlotBy
>
> > Case 1              ' by xlColumns
> > SrcRange.Interior.ColorIndex =3D 7
> > TheChart.SeriesCollection(1).Name =3D "=3D" & ActiveSheet.Name & "!R" &
> > UserRow & "C1"
> > TheChart.SetSourceData Source:=3DSrcRange, PlotBy:=3DPlotBy
> > TheChart.SeriesCollection.NewSeries
> > TheChart.SeriesCollection(2).Name =3D "=3D" & ActiveSheet.Name & "!R25C=
1"
> > TheChart.SeriesCollection(2).Values =3D "=3D" & ActiveSheet.Name & "!R2=
5C"
> > & StartCol & ":R25C" & StartCol + EndCol - 2
> > TheChart.ApplyCustomType ChartType:=3DxlBuiltIn, TypeName:=3D _
> >        "Line - Column on 2 Axes"
>
> >        With TheChart
> >        .Axes(xlValue, xlSecondary).HasTitle =3D True
> >        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =3D
> > "United States"
> >    End With
>
> > 'set label for each series
> > Set xlblrng =3D ActiveSheet.Range(Cells(15, StartCol), Cells(15,
> > StartCol + EndCol - 2))
> > For Each srs In TheChart.SeriesCollection
> >    srs.XValues =3D xlblrng
> > Next
> > TheChart.HasTitle =3D True
> > TheChart.Axes(xlCategory).AxisTitle.Text =3D ActiveSheet.Range("B77") &
> > vbCr & ActiveSheet.Range("B78")
> > '----------------------------------------
> > Case 2              ' by xlrows
> > SrcRange.Interior.ColorIndex =3D 4
> > TheChart.SetSourceData Source:=3DSrcRange, PlotBy:=3DPlotBy
>
> > 'set label for each series
> > Set xlblrng =3D ActiveSheet.Range(Cells(16, 1), Cells(16 + krows, 1))
> > For Each srs In TheChart.SeriesCollection
> > On Error Resume Next
> >    srs.XValues =3D xlblrng
> > Next
> > TheChart.HasTitle =3D True
> > TheChart.Axes(xlCategory).AxisTitle.Text =3D ActiveSheet.Range("B75") &
> > vbCr & ActiveSheet.Range("B76")
>
> > End Select
> > '=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
> > Select Case ActiveSheet.Name
> > Case Is =3D "Region_Year_o_Year"
>
> > TheChart.SeriesCollection(2).Name =3D "=3D" & ActiveSheet.Name & "!R25C=
1"
> > TheChart.SeriesCollection(2).Values =3D "=3D" & ActiveSheet.Name & "!R2=
5C"
> > & StartCol & ":R25C" & StartCol + EndCol - 2
> > TheChart.ApplyCustomType ChartType:=3DxlBuiltIn, TypeName:=3D _
> >        "Line - Column on 2 Axes"
>
> >        With TheChart
> >        .Axes(xlValue, xlSecondary).HasTitle =3D True
> >        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =3D
> > "United States"
> >    End With
>
> > ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex =3D 15
> > ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex =3D 15
> >    If PlotBy =3D 1 Then
> >        kMajorUnit =3D 1
> >        kMinorUnit =3D 1
> >        TkLblSpc =3D 1    ' horizontal =3D timeseries
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlYears
> >        kMinorUnitScale =3D xlYears
> >        ChartTitleText =3D "HPA Time-Series for " & Cells(UserRow,
> > 1).Value
> >    Else
> >        kMajorUnit =3D 1
> >        kMinorUnit =3D 1
> >        TkLblSpc =3D 1 ' vertical =3D cross section
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlYears
> >        kMinorUnitScale =3D xlYears
> >        ChartTitleText =3D "HPA Cross-Section for Year " &
> > Format(Cells(15, UserCol).Value, "yyyy")
> >    End If
>
> > TheChart.HasTitle =3D True
> > TheChart.Axes(xlCategory).AxisTitle.Text =3D ActiveSheet.Range("B75") &
> > vbCr & ActiveSheet.Range("B76")
>
> > Case Is =3D "State_Year_o_Year"
>
> > TheChart.SeriesCollection(2).Name =3D "=3D" & ActiveSheet.Name & "!R67C=
1"
> > TheChart.SeriesCollection(2).Values =3D "=3D" & ActiveSheet.Name & "!R6=
7C"
> > & StartCol & ":R67C" & StartCol + EndCol - 2
> > TheChart.ApplyCustomType ChartType:=3DxlBuiltIn, TypeName:=3D _
> >        "Line - Column on 2 Axes"
>
> >        With TheChart
> >        .Axes(xlValue, xlSecondary).HasTitle =3D True
> >        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =3D
> > "United States"
> >    End With
>
> > ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex =3D 15
> > ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex =3D 15
> >    If PlotBy =3D 1 Then
> >        kMajorUnit =3D 1
> >        kMinorUnit =3D 1
> >        TkLblSpc =3D 1    ' horizontal =3D timeseries
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlYears
> >        kMinorUnitScale =3D xlYears
> >        ChartTitleText =3D "HPA Time-Series for " & Cells(UserRow,
> > 1).Value
> >    Else
> >        kMajorUnit =3D 1
> >        kMinorUnit =3D 1
> >        TkLblSpc =3D 1 ' vertical =3D cross section
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlYears
> >        kMinorUnitScale =3D xlYears
> >        ChartTitleText =3D "HPA Cross-Section for Year " _
> >                & Format(Cells(15, UserCol).Value, "yyyy")
> >    End If
>
> > Case Is =3D "Region_Qrtr_o_Qrtr"
>
> > TheChart.SeriesCollection(2).Name =3D "=3D" & ActiveSheet.Name & "!R25C=
1"
> > TheChart.SeriesCollection(2).Values =3D "=3D" & ActiveSheet.Name & "!R2=
5C"
> > & StartCol & ":R25C" & StartCol + EndCol - 2
> > TheChart.ApplyCustomType ChartType:=3DxlBuiltIn, TypeName:=3D _
> >        "Line - Column on 2 Axes"
>
> >        With TheChart
> >        .Axes(xlValue, xlSecondary).HasTitle =3D True
> >        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =3D
> > "United States"
> >    End With
>
> > ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =3D
> > 15
> > ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex =3D 15
> >    If PlotBy =3D 1 Then
> >        kMajorUnit =3D 3
> >        kMinorUnit =3D 3
> >        TkLblSpc =3D 1    ' horizontal =3D timeseries
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlMonths
> >        kMinorUnitScale =3D xlMonths
> >        ChartTitleText =3D "HPA Time-Series for " & Cells(UserRow,
> > 1).Value
> >    Else
> >        kMajorUnit =3D 3
> >        kMinorUnit =3D 3
> >        TkLblSpc =3D 1 ' vertical =3D cross section
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlMonths
> >        kMinorUnitScale =3D xlMonths
> >        ChartTitleText =3D "HPA Cross-Section for Quarter ending " _
> >                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
> >    End If
>
> > Case Is =3D "State_Qrtr_o_Qrtr"
>
> > TheChart.SeriesCollection(2).Name =3D "=3D" & ActiveSheet.Name & "!R67C=
1"
> > TheChart.SeriesCollection(2).Values =3D "=3D" & ActiveSheet.Name & "!R6=
7C"
> > & StartCol & ":R67C" & StartCol + EndCol - 2
> > TheChart.ApplyCustomType ChartType:=3DxlBuiltIn, TypeName:=3D _
> >        "Line - Column on 2 Axes"
>
> >        With TheChart
> >        .Axes(xlValue, xlSecondary).HasTitle =3D True
> >        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =3D
> > "United States"
> >    End With
>
> > ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =3D
> > 15
> > ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex =3D 15
> >    If PlotBy =3D 1 Then
> >        kMajorUnit =3D 3
> >        kMinorUnit =3D 3
> >        TkLblSpc =3D 1    ' horizontal =3D timeseries
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlMonths
> >        kMinorUnitScale =3D xlMonths
> >        ChartTitleText =3D "HPA Time-Series for " & Cells(UserRow,
> > 1).Value
> >    Else
> >        kMajorUnit =3D 3
> >        kMinorUnit =3D 3
> >        TkLblSpc =3D 1 ' vertical =3D cross section
> >        kChartType =3D 51
> >        kMajorUnitScale =3D xlMonths
> >        kMinorUnitScale =3D xlMonths
> >        ChartTitleText =3D "HPA Cross-Section for Quarter ending " _
> >                & Format(Cells(15, UserCol).Value, "mmm-yyyy")
>
> >    End If
>
> > Case Is =3D "Region_Month_o_Month"
> > TheChart.SeriesCollection(2).Name =3D "=3D" & ActiveSheet.Name & "!R25C=
1"
> > TheChart.SeriesCollection(2).Values =3D "=3D" & ActiveSheet.Name & "!R2=
5C"
> > & StartCol &
>
> ...
>
> read more =BB- Hide quoted text -
>
> - Show quoted text -


0
3/22/2007 3:52:45 AM
Reply:

Similar Artilces:

How to Switch off no access symbol on junk mail filter,
I use outlook and seem to have switched off the junk mail filter. it displays a no-access sign over the filter and I can find no way of getting in to it even in tools. Junk mail goes into this folder and immediatley dissapears. Any ideas? What type of email account? Do you have any other spam filters installed, such as included with your antivirus software? The junk folder's icon has the No symbol on it. The screenshot at http://xsolive.com/Outlook%20Screen%20shots/Forms/DispForm.aspx?ID=124 has the junk folder in view - if that is the icon you see, its correct. -- Di...

Stacked bar charts side by side
I want to create a chart with stacked bar charts, by week. One stacked bar chart for open risks and issues and the other for closed risks and issues. I also want to plot a line graph for outstanding items (which I think I can do). Does anyone know how I achieve this result. Your suggestions would be gratefully received. -- Many thanks Adrian. Hi, See http://peltiertech.com/WordPress/clustered-stacked-column-charts/ Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Pichincha" <Pichincha@discussions.microsoft.com> wrote in message news:FB4...

Adding numbers in a column
I've been noticing that Excel 2003 is incorrectly adding up the numbers in a column. Anyone know why this is and is there a fix for it? On Fri, 08 Sep 2006 16:01:07 +0100, Holden Caulfield = <HoldenCaulfield@discussions.microsoft.com> wrote: > I've been noticing that Excel 2003 is incorrectly adding up the number= s = > in a > column. Anyone know why this is and is there a fix for it? a small example would be nice. Mine works fine - with the numbers I get it to add -- = Steve (3) Are these numbers with decimals, by any chance? The culprit it always the di...

copy the data to all the columns
Hi, I have 5000 names in a excel sheet and after every name there is blank row, i need macro to copy the DEPT and CCentre data to all the names example as shown below Name DEPT CCENTRE A1 A P A1 S U A1 D Y A1 R K B2 A P B2 S U B2 D Y B2 R K B2 Hi All, Please Tell Me [ How to open Hidden excel Sheet But Only I Follow the Hyperlink ] & Please Send me Screen Shot ok Thanks Abhijeet Try something like this. the code put the results in the DESTSHT which is sheet 2. VBA Code: -------------------- Sub CopyNames() Set Sou...

Modifying a Chart Object Name #2
Im making a MACRO to alter montly many different files with the same paddern. In these files there are many sheets with a few Charts. Those charts are two for INCOME and two for PROFIT in each sheet. My problem is that the name of the Charts in the macro appear with the number that Excel applyed to it and i would like to make standar names for each Chart. This way there would be only 4 chart names for each sheet, instead of 16 diferent chart names for the 4 sheets in the file im working. This is how the part of the macro looks: - ActiveSheet.ChartObjects("Gr�fico 8").Activate- ....

add a row or column to a single cell in exel
I am trying to split a single cell into 2 rows can you help If I understood correctly you want to enter an entry in two lines in a cell at appropirate place hit lt+enter and again hit enter for e.g venkat (alt+enter) raman(enter) "trev" <trev@discussions.microsoft.com> wrote in message news:89B2C15C-057F-43B9-8282-37923D5EA397@microsoft.com... > I am trying to split a single cell into 2 rows can you help ...

Excel won't properly autofit row height
When I'm attempting to autofit row height on a row containg cells which use word wrap, Excel often leaves an extra space at the top of the cell. Is there a way to fix this - other than manually? Thanks for your help. Michael mnagan@omniprint.net At first I could duplicate what you're saying. Researching the problem suggested that it has to do with MicroSnot basing it on a minimum automatic row height of 12.75, which in turn is based on the default font size of Arial 10. Thus the space. So I changed my default font size to 8 (Tools|Options) and re-started Excel. The idea was to...

copy a drop down menu so it appears in each row of a column?
I'm trying to create a spread sheet to enter data into and i've figured out how to make a dropdown list to simplify data entry. But how can i make the drop down list appear in evey row of a column? I'm an excel newbie! thanks for your help! Make your list somewhere and then use Data-Validation-and where it says "any value" choose "list" and Then choose the source of the list. When you have done this for one cell, copy and then paste special and choose validation. Hope this helps "Vanmessa" wrote: > I'm trying to create a spread sheet...

Overlaying 2 Data Series with Different Time (X) Axis
Win XP Pro Excel 2002 SP3 Hi, I need to chart (overlay) 2 data series over the same period of time, say January 1, 2010 to December 31, 2010. The problem is twofold: 1) The series are very much out of the same range, so I have to plot 1 on primary and other on secondary axis (otherwise, the Y axis scale does not allow a proper comparison) I "could" resolve that by "normalizing" the 2 series so start at 100 each but it would lose value with normalized numbers as opposed to the actual numbers. Normalizing both series would allow using only a Primary axis. 2) Both ...

identically size merged cells for sorting rows
I am using excel 2000 and I have been trying to sort rows based on the content of 3 colums. But every time I try to do this I keep getting told that the merged cells need to be identically sized, I have no idea what this means and how to change the cells so that they are identically sized. Can anyone help? I think that's mean you have merged cells in your selection. You have to delete the merge before sorting. I'm not sure if there is other solution without deleting the merge. -- hideki ------------------------------------------------------------------------ hideki's P...

manipulating textbox in chart
Hi, I want to manipulate the text of a textbox (created with drawing) on a chart. How to do this in VB. How to refer to the textbox? Is it possible at all? I tried to create a textbox control on the chart. This works fine and I also can access it by VB but since the chart is linked to a word-file, the textbox control is not visible in word. Any idea? Thanx, Peter ...

Contour Chart
Is there a way to find the percentage of the areas of each shaded area of a contour chart. I have a chart that is simply 3 colors. Green, red and blue (Normal, Hot, Cold). The chart is generated by 30 data inputs. A Surface/Contour chart is generated by excel. I have a VBA app where I need to determe the percentage co cold, hot and normal temperatures. Can you suggest a way to do this? Thanks ...

Excel Charts X Axis values as dd/mm/yy hh:mm
I have a table with readings taken at irregular intervals. I want the data points to be plotted along a continuous axis but when I try to do this all the data appears against the date only and the time is ignored. I have tried setting the x axis as time series but this doesn't work. I can't change the units to anything but days. Can anyone suggest a solution. TIA PeterS Peters: You are using a Line chart. Line charts do not let you plot less than 1 day intervals. I use XY (Scatter) plots for data with hours:minutes. I suggest you change your chart type to XY then set your X Axis ...

Renaming Columns??
Can you rename the columns and or rows....so instead of it reading 1, 2 3 etc, you can customize it to say Rent, Income, etc. Thank -- hippi ----------------------------------------------------------------------- hippie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1622 View this thread: http://www.excelforum.com/showthread.php?threadid=27633 Hi no, you can't. You can though use row 1 / column A and use 'Windows - Freeze Panes' after moving the cursor to cell B2 to always see this row/column -- Regards Frank Kabel Frankfurt, Germany hippie wr...

Overlapping Time Chart?
Is there a way to graph a chart by date (x axis) that shows stacked bars for overlapping time? Ideally, I would have the columns like below: Date ShoppingStart TimeShopping StoreAsStart TimeInStoreA StoreBStart TimeInStoreB 1/1/08 7:00AM 5 hrs 8:32AM 45 min 10:00AM 1.5 hrs Then you would see a bar for that date, with 1 big bar for the day, with 2 colors inside that bar representing the time in shop a and the time in shop b. The y axis would be hours from say 6:00 AM - 7:00 PM. Does...

Pivot charts in 2007 vs 200x
Hello to all, In older versions of Excel, you always had the page list in a pivot chart. Where have they gone to in Excel 2007. If they are no more available directly in the chart, is there a way to get around this anoyance? Thanks for your answers. Stephane "Steve" Halle Hi, This has been replace by the PivotChart Filter Pane which you can expose by selecting the pivot chart and choosing PivotChart Tools, Analyze, PivotChart Filter. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "billardquebec@gmail.com" wrote: > Hello to all, &g...

Problems creating a line chart or scatter graph
Hi, I have the following data, Newspaper - FT, Mirror, Sun Sector - Gambling, Healthcare, Retail Date range by month - Jan, Feb, March etc. I would like to show how many articles were written by each newspaper in each sector on a timeline but have no idea how to order my data in otder for this to happen. Anyone able to advise? -- Nat On Wed, 2 Apr 2008, in microsoft.public.excel.charting, Natty <natwykes@hotmail.com> said: >I have the following data, > >Newspaper - FT, Mirror, Sun >Sector - Gambling, Healthcare, Retail >Date range by month - Jan, Feb, March etc. &...

In Excel, how can print columns of data?
I have a long skinny Excel worksheet. I'd like to print it to one page, however this crunches it down to one column. Is there any way to force Excel to make a mid-page break and create another column to continue with the data? I'd copy the excel data into MSWord and print from there. Word has built in support for multiple columns (Format|Columns, IIRC). But if you want to stick with excel... David McRitchie has some code to do this kind of thing at: http://www.mvps.org/dmcritchie/excel/snakecol.htm kingant wrote: > > I have a long skinny Excel worksheet. I'd like...

Copy varaible no. of rows to another woorkbook
The following should work but it only copies cell A1 from the SSh (source) to cell A1 on the DSh (destination). Sub MoveData() Dim SSh As Worksheet 'source sheet Dim DSh As Worksheet 'target sheet Dim LastRow As Long Dim CopyRange As String Set SSh = ActiveWorkbook.ActiveSheet Set DSh = Workbooks("CMS Register of ClaimsAuto").Worksheets("Summary") CopyRange = SSh.Range("A2:E" & Rows.count).End(xlUp).Copy DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues) End Sub Where did I go wrong? -- Traa Dy Liooar ...

Net profit chart
Hi I need your help regarding a problem with Excel. I am trying to create a net cash flow chart, with the net cash as the vertical axis and time as the horizontal axis; I have the values in the table. The real problem now the chart wizard doesn’t create the chart as I want I have 2 values on week 6 for example one is negative the other is positive, I want it drawn as a single line that goes down with the negative value than up to the positive value. After that I want to calculate the areas both positive and negative so I can calculate the net profit. Thanks in advance Sherif P.S. Be...

column charts with multiple data sets
My data set is below. I want to show column charts: The first one would be Product A for August made up of data for versions 1-4, column two would be Product A for September made up of data for versions 1-4. The third column would be Product B for August made up of data for versions 1-4, column four would be Product B for September made up of data for versions 1-4. Product Version August September A 1 30,812 365,874 A 2 59,650,018 56,404,138 A 3 29,931,636 28,053,084 A 4 71,972 62,352 B 1 7,094,967 11,718,925 B 2 9,588,255 ...

copy date from one column to another
I want to copy data from one column to another column, but add the copied value to existing data. The field I'm copying from contains dates. The field I'm copying to contains text. For example: Before the copy: Column A Column B 3/21/2008 Lorem Ipsum dolor...etc. After copy: Column B Lorem Ipsum dolor...etc. Date in: 3/21/2008 How can I accomplish this? Thanks for any help. Regards. Sub addtocolb() For Each c In Range("a2:a4") c.Offset(, 1) = c.Offset(, 1) & _ " Date in: " & Format(c, "mm/dd/yyyy&...

Can I expand & shrink columns?
Hello everyone, I want to know it there is a way to pick 2 or 4 columns and have them expand when I click on them. So I want it where I can click on them and they expand to normal size and if I click on them again the columns will shrink back to a smaller size. I hope I'm making sense. I don't want to have to widened the column by clicking in between each column, I know how to do that. I'm trying to create sort of a daily planner, so I want it to where the days I'm not looking at are like shrunk to a smaller size Right click sheet tab>view code>...

Randomly Sort a Series of Numbers
I have a column of numbers (for ex.: 1-100) which I desire to sort randomly. How would I go about doing this? I am appreicative of any assistance. One option: If your data is in A1:A100, put =rand() in B1 and copy down. Sort by Column B. tj "Shoelaces" wrote: > I have a column of numbers (for ex.: 1-100) which I desire to sort randomly. > How would I go about doing this? > > I am appreicative of any assistance. > > In an adjoining column, enter this formula: =RAND() And *double click* on the "fill handle" to automatically copy it as far do...

Displaying symbols in charts with tables
I am trying to create a chart of percentages with the data table displayed with the dhart in Excel 2003. The problem is that I have some symbols in the table (e.g., <1%), which when charted are treated as text and simply show up as a zero in the data table. I would appreciate any suggestions for getting these symbols to appear correctly in the data table displayed with the chart. Thanks! -Aaron jageorge, Unfortunately, you have very little control over the data tables that appear as an option. You can get around this limitation by using worksheet cells as a data table. This exa...