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
