Change title of pivot chart
I cannot seem to figure out how to get the title of my pivot chart to change and show up. Pseudocode below:
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim PChart As ChartObject
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 3
'Set data range
Set PRange = .Cells(1, 1).Resize(lastRow - 3, 11)
Set PCache = wb2.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _
TableName:="VarietyByTerritoryTable")
This is all working as intended. The lastRow variable is finicky but gets the job done. This is all in a With block. I have some code after this that adds fields to the PTable but is working fine so I am choosing to omit. Now I create the chart. Note, actually creating the chart works fine, I just cant seem to get the title to show up.
Set PChart = wb2.Sheets(1).ChartObjects.Add(300, 200, 550, 200)
With PChart
.Chart.SetSourceData PTable.TableRange2
.Chart.ChartType = xlAreaStacked
.Name = "Territory"
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = strVar & " in " & strTerr
End With
Error shows up in the .SetElement() line. A couple of questions:
a) Does anyone know how to fix this? I think it should be relatively simple, but I have spent the last few hours looking around online and couldn't find much :/
b) what do the numbers in the .ChartObjects.Add(##, ##, ###, ##) do? They change the location I assume. Do the last two deal with the size of the chart?
c) Can I make the title of the chart be the name from TableName in the Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _ bit? If so, can I have spaces in the TableName part? (i.e. change to
TableName:="VarietyByTerritoryTable")TableName := "Variety By Territory")
d) same question but with the PChart.Name. Can I make the title off of this, and if so, should I avoid spaces?
Thanks everyone!
excel vba excel-vba pivot-table
edited Dec 28 '18 at 1:54
L42
16.5k73154
asked Dec 27 '18 at 22:05
chairSitter
466
add a comment |
I cannot seem to figure out how to get the title of my pivot chart to change and show up. Pseudocode below:
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim PChart As ChartObject
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 3
'Set data range
Set PRange = .Cells(1, 1).Resize(lastRow - 3, 11)
Set PCache = wb2.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _
TableName:="VarietyByTerritoryTable")
This is all working as intended. The lastRow variable is finicky but gets the job done. This is all in a With block. I have some code after this that adds fields to the PTable but is working fine so I am choosing to omit. Now I create the chart. Note, actually creating the chart works fine, I just cant seem to get the title to show up.
Set PChart = wb2.Sheets(1).ChartObjects.Add(300, 200, 550, 200)
With PChart
.Chart.SetSourceData PTable.TableRange2
.Chart.ChartType = xlAreaStacked
.Name = "Territory"
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = strVar & " in " & strTerr
End With
Error shows up in the .SetElement() line. A couple of questions:
a) Does anyone know how to fix this? I think it should be relatively simple, but I have spent the last few hours looking around online and couldn't find much :/
b) what do the numbers in the .ChartObjects.Add(##, ##, ###, ##) do? They change the location I assume. Do the last two deal with the size of the chart?
c) Can I make the title of the chart be the name from TableName in the Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _ bit? If so, can I have spaces in the TableName part? (i.e. change to
TableName:="VarietyByTerritoryTable")TableName := "Variety By Territory")
d) same question but with the PChart.Name. Can I make the title off of this, and if so, should I avoid spaces?
Thanks everyone!
excel vba excel-vba pivot-table
edited Dec 28 '18 at 1:54
L42
16.5k73154
asked Dec 27 '18 at 22:05
chairSitter
466
add a comment |
I cannot seem to figure out how to get the title of my pivot chart to change and show up. Pseudocode below:
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim PChart As ChartObject
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 3
'Set data range
Set PRange = .Cells(1, 1).Resize(lastRow - 3, 11)
Set PCache = wb2.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _
TableName:="VarietyByTerritoryTable")
This is all working as intended. The lastRow variable is finicky but gets the job done. This is all in a With block. I have some code after this that adds fields to the PTable but is working fine so I am choosing to omit. Now I create the chart. Note, actually creating the chart works fine, I just cant seem to get the title to show up.
Set PChart = wb2.Sheets(1).ChartObjects.Add(300, 200, 550, 200)
With PChart
.Chart.SetSourceData PTable.TableRange2
.Chart.ChartType = xlAreaStacked
.Name = "Territory"
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = strVar & " in " & strTerr
End With
Error shows up in the .SetElement() line. A couple of questions:
a) Does anyone know how to fix this? I think it should be relatively simple, but I have spent the last few hours looking around online and couldn't find much :/
b) what do the numbers in the .ChartObjects.Add(##, ##, ###, ##) do? They change the location I assume. Do the last two deal with the size of the chart?
c) Can I make the title of the chart be the name from TableName in the Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _ bit? If so, can I have spaces in the TableName part? (i.e. change to
TableName:="VarietyByTerritoryTable")TableName := "Variety By Territory")
d) same question but with the PChart.Name. Can I make the title off of this, and if so, should I avoid spaces?
Thanks everyone!
excel vba excel-vba pivot-table
edited Dec 28 '18 at 1:54
L42
16.5k73154
asked Dec 27 '18 at 22:05
chairSitter
466
I cannot seem to figure out how to get the title of my pivot chart to change and show up. Pseudocode below:
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim PChart As ChartObject
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 3
'Set data range
Set PRange = .Cells(1, 1).Resize(lastRow - 3, 11)
Set PCache = wb2.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _
TableName:="VarietyByTerritoryTable")
This is all working as intended. The lastRow variable is finicky but gets the job done. This is all in a With block. I have some code after this that adds fields to the PTable but is working fine so I am choosing to omit. Now I create the chart. Note, actually creating the chart works fine, I just cant seem to get the title to show up.
Set PChart = wb2.Sheets(1).ChartObjects.Add(300, 200, 550, 200)
With PChart
.Chart.SetSourceData PTable.TableRange2
.Chart.ChartType = xlAreaStacked
.Name = "Territory"
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = strVar & " in " & strTerr
End With
Error shows up in the .SetElement() line. A couple of questions:
a) Does anyone know how to fix this? I think it should be relatively simple, but I have spent the last few hours looking around online and couldn't find much :/
b) what do the numbers in the .ChartObjects.Add(##, ##, ###, ##) do? They change the location I assume. Do the last two deal with the size of the chart?
c) Can I make the title of the chart be the name from TableName in the Set PTable = PCache.CreatePivotTable(TableDestination:=.Cells(lastRow, 6), _ bit? If so, can I have spaces in the TableName part? (i.e. change to
TableName:="VarietyByTerritoryTable")TableName := "Variety By Territory")
d) same question but with the PChart.Name. Can I make the title off of this, and if so, should I avoid spaces?
Thanks everyone!
excel vba excel-vba pivot-table
excel vba excel-vba pivot-table
edited Dec 28 '18 at 1:54
L42
16.5k73154
asked Dec 27 '18 at 22:05
chairSitter
466
edited Dec 28 '18 at 1:54
L42
16.5k73154
asked Dec 27 '18 at 22:05
chairSitter
466
edited Dec 28 '18 at 1:54
L42
16.5k73154
edited Dec 28 '18 at 1:54
L42
16.5k73154
edited Dec 28 '18 at 1:54
L42
16.5k73154
16.5k73154
asked Dec 27 '18 at 22:05
chairSitter
466
asked Dec 27 '18 at 22:05
chairSitter
466
asked Dec 27 '18 at 22:05
chairSitter
466
466
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You are missing the
.Chartin yourSetElementline. So try this:
.Chart.SetElement (msoElementChartTitleAboveChart)
.Chart.ChartTitle.Text = strVar & " in " & strTerr
For
ChartObjects Methodyou are correct, the last 2 value refers to the width and height.You can certainly make the name of the chart same with the table name and yes, spaces are allowed in pivot table names.
Same for chart titles, spaces are ok.
answered Dec 28 '18 at 1:53
L42
16.5k73154
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53951323%2fchange-title-of-pivot-chart%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You are missing the
.Chartin yourSetElementline. So try this:
.Chart.SetElement (msoElementChartTitleAboveChart)
.Chart.ChartTitle.Text = strVar & " in " & strTerr
For
ChartObjects Methodyou are correct, the last 2 value refers to the width and height.You can certainly make the name of the chart same with the table name and yes, spaces are allowed in pivot table names.
Same for chart titles, spaces are ok.
answered Dec 28 '18 at 1:53
L42
16.5k73154
add a comment |
You are missing the
.Chartin yourSetElementline. So try this:
.Chart.SetElement (msoElementChartTitleAboveChart)
.Chart.ChartTitle.Text = strVar & " in " & strTerr
For
ChartObjects Methodyou are correct, the last 2 value refers to the width and height.You can certainly make the name of the chart same with the table name and yes, spaces are allowed in pivot table names.
Same for chart titles, spaces are ok.
answered Dec 28 '18 at 1:53
L42
16.5k73154
add a comment |
You are missing the
.Chartin yourSetElementline. So try this:
.Chart.SetElement (msoElementChartTitleAboveChart)
.Chart.ChartTitle.Text = strVar & " in " & strTerr
For
ChartObjects Methodyou are correct, the last 2 value refers to the width and height.You can certainly make the name of the chart same with the table name and yes, spaces are allowed in pivot table names.
Same for chart titles, spaces are ok.
answered Dec 28 '18 at 1:53
L42
16.5k73154
You are missing the
.Chartin yourSetElementline. So try this:
.Chart.SetElement (msoElementChartTitleAboveChart)
.Chart.ChartTitle.Text = strVar & " in " & strTerr
For
ChartObjects Methodyou are correct, the last 2 value refers to the width and height.You can certainly make the name of the chart same with the table name and yes, spaces are allowed in pivot table names.
Same for chart titles, spaces are ok.
answered Dec 28 '18 at 1:53
L42
16.5k73154
answered Dec 28 '18 at 1:53
L42
16.5k73154
answered Dec 28 '18 at 1:53
L42
16.5k73154
answered Dec 28 '18 at 1:53
L42
16.5k73154
16.5k73154
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53951323%2fchange-title-of-pivot-chart%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
