How do I automatically pull a chart title (from a data table on another sheet) and make it appear on a graph that has both cluster columns and a line?
Initially, when I only had a cluster column, the chart title showed up automatically. Now, after adding the line as a second X series the chart title disappears. I want to add in a code so that the chart title still shows up.
The code is a bit complex:
1. the data table has about 80 rows of independent variables (in sheet called "DataTable")
2. If the user wants to see a graph for the independent variable, then there is a "1" denoting "yes" (in the DataTable sheet, shown in red text here)
3. The code then graphs an individual cluster-line graph for each variable (on the "Graphs" sheet).
4. Finally, I need help: how do I change this code so that the chart title shows up for each graph? (the chart title is already in "DataTable" -- column O and the row of the independent variable called "CurRow" here). Near the bottom I have red text for the Chart formatting.
Appreciate your help, thanks
Initially, when I only had a cluster column, the chart title showed up automatically. Now, after adding the line as a second X series the chart title disappears. I want to add in a code so that the chart title still shows up.
The code is a bit complex:
1. the data table has about 80 rows of independent variables (in sheet called "DataTable")
2. If the user wants to see a graph for the independent variable, then there is a "1" denoting "yes" (in the DataTable sheet, shown in red text here)
3. The code then graphs an individual cluster-line graph for each variable (on the "Graphs" sheet).
4. Finally, I need help: how do I change this code so that the chart title shows up for each graph? (the chart title is already in "DataTable" -- column O and the row of the independent variable called "CurRow" here). Near the bottom I have red text for the Chart formatting.
Appreciate your help, thanks
Code:
Dim CurRow As Integer
Dim TableRow As Integer, TableNum As Integer
Dim RowIncrement As Integer
Dim TableColumn As Integer
Sheets("DataTable").Select
CurRow = 42
ChartNum = 1
' TotalCharts = Range("N129").Value
TableRow = 21
RowIncrement = 24
TableColumn = 7
While (Range("N" & CurRow) <> "")
If ([COLOR="Red"]Range("N" & CurRow) = 1[/COLOR]) Then [COLOR="Red"]'selects variables[/COLOR]
Sheets("Graphs").Select
Range("'DataTable'!$AN" & CurRow).Copy
Range("G" & LTrim(Str(TableRow + 2))).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G" & LTrim(Str(TableRow + 2)) & ":AD" & LTrim(Str(TableRow + 2))).Select
Application.CutCopyMode = False
Selection.FillRight
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'DataTable'!$O$" & CurRow & ":$AM$" & CurRow)
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Parent.Left = 50
ActiveChart.Parent.Top = 400 + 300 * (ChartNum - 1)
ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows
ActiveChart.SetElement (msoElementLegendNone)
ActiveChart.SeriesCollection(1).XValues = "='DataTable'!$P$41:$AM$41"
Range("G" & LTrim(Str(TableRow + 2)) & ":AD" & LTrim(Str(TableRow + 2))).Copy
[COLOR="red"]ActiveChart.Paste
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLine[/COLOR]
With ActiveChart
.HasTitle = True
[COLOR="red"].ChartTitle.Text = "='DataTable'!$O$ & CurRow"[/COLOR] '[COLOR="red"]Title source is the Data table but chart titles do not appear [/COLOR]
End With
