Chart Object Caption

  • Thread starter Thread starter Jeff Smith
  • Start date Start date
J

Jeff Smith

I have a control that brings up a graph. The Excel name for the graph shown
in the caption is "Sheet 1 Chart 109".

My question is how can I give the chart a new caption?
and /or
How can I suppress the caption (for other chart objects in the workbook)

I have tried ActiveChart.Caption = "NewTitle" but VBA wouldn't accept this.

Would someone be kind enough to help resolve this.
(I'm very impressed with this News Group - I am learning new techniques,
almost daily)

TIA

regards

Jeff Smith
 
Try the following two options. The first doesn't require
that the chart be selected and assumes that it is a
worksheet based (embedded) chart. It is also assumed that
it is the first chart added to the worksheet (if more than
one). Change the ChartObject index number (in
parenthesis) to suit if necessary.

Also note that the line ".HasTitle = True" is only
necessary if no title already exists. Change to False to
suppress the chart title.

Preferred code:
Sub ChangeChartTitle()
With ActiveSheet.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Characters.Text = "Annual Widget Sales"
End With
End Sub

Alternate code:
Sub ChangeChartTitle()
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Annual Widget Sales"
End With
End Sub

Hope it does the job.

Regards,
Greg
 
Greg, thanks for this. The code added a title inside the chart as a title
(which is an *Excellent* innovation. The blue banner itself however still
says "Sheet 1.xls Chart 109". Can you help with the banner name change?

I really appreciate you taking the time to help.

sincerely

Jeff Smith
 
Hi Jeff,

Sorry, I guess I misinterpretted. Your macro seems to
activate the chart window (???) or perhaps you have some
setting I'm unaware of that brings this about.

Suggested code to show/hide the chart window:
ActiveChart.ShowWindow = True (or False to suppress)

Ensure that the chart window is first activated before
executing the following line or it will change the caption
for the worksheet window. Suggested code to change the
chart window caption.

ActiveWindow.Caption = "Annual Widget Sales"

I have to go out on an errand and won't be back till much
later. If this doesn't fix it I hope someone else will
give it a shot.

Regards,
Greg
 
Jeff -

Sounds like the chart's window is being activated. You can change the
chart object's name with this line:

ActiveChart.Parent.Name = "New Name"

but that doesn't change the chart window caption. With he chart window
showing, this changes the window caption:

ActiveWindow.Caption = "New Window Caption"

- Jon
 
Thanks Jon,

I have found a new problem - it works great - just as I visualised until I
protected the sheet (I have to protect all the formulas from inept users).
With protect on, the chart disappears in a millisecond as soon as I
re-protect using VB after calling up the chart. I have been fiddling with
this problem for about 2 hrs and close to abandoning or just use
print-preview of the chart. I do learn from these experiences though.

Can you (or anyone else learning from this problem) help?

TIA

Jeff Smith
 
Jeff,

You say "I have a control that brings up a graph." I
think the manner in which you "bring up the graph" is the
cause of this atypical situation. Perhaps post this
code. Also, where is the source data located?

Regards,
Greg
 
Greg,

Control Button is in the same sheet as the chart (which is out of the
viewing area) and uses the code below. The code is located thus:

Private Sub CommandButton3_Click()

Sheets("SheetNames").Unprotect Password:="MyPassword"
ActiveSheet.ChartObjects("Chart 109").Activate
ActiveChart.ChartArea.Select
ActiveChart.ShowWindow = True


It all works beautifully until the protect code is inserted at the end.
i.e. Sheets("SheetNames").Protect Password:="MyPassword"

Any help appreciated.

sincerely

Jeff Smith
 
Jeff,

You are apparently keeping the chart off screen and using
the command button to activate it as well as activate the
chart window in order to bring it into view. I've not
used this technique; and, at least at first blush, am not
a fan of it. However, don't assume that I'm an expert on
the subject.

I would suggest toggling the Visible property of the chart
instead. Or toggling the Hidden property of the rows that
contain it. The second option allows you to toggle the
visible status of other things on the worksheet that are
in the same rows as the chart. For instance, message text
or controls that automate scaling etc. Just my opinion.

The following code is suggested as an alternative:

Private Sub CommandButton3_Click()
With Sheets("SheetNames")
..Unprotect "MyPassword"
With .ChartObjects("Chart 109")
..Visible = Not .Visible
If .Visible Then CommandButton3.Caption = "Hide Chart" _
Else CommandButton3.Caption = "Show Chart"
End With
..Protect "MyPassword"
End With
End Sub

The following seems to do what you want:

Private Sub CommandButton3_Click()
With ActiveSheet
.Unprotect "MyPassword"
.ChartObjects("Chart 109").Activate
.Protect "MyPassword"
End With
ActiveChart.ShowWindow = True
End Sub

Regards,
Greg
 
Greg,

Thanks for this. I am in New Zealand so time difference impacted (delay).
I shall try this new code. Your assistance is highly valued.

sincerely

Jeff Smith
 
Greg,

I have used both sets of code. The "bottom" one seemed to need and End With
before End Sub. But it only worked if I suppressed the re-protect bit.
This is the first problem I encountered with the initial code. I wish I
didn't have to protect the sheet :- (. I prefer to persevere with getting
the window to work because it is in colour. The back-up option, print
preview is in B&W.

If you can see why re-protecting defeats the macro and have a solution, it
will be gratefully received, otherwise I'll just drop this feature for now.

I DO appreciate your help.

sincerely

Jeff Smith
 
Jeff -

You can set up the chart so that your chart window will still work.
First, you need to unlock the Shapes object that contains the chart.
Then set the ProtectSelection property of the chart to True to prevent
people from monkeying with it. This little procedure unprotects the
wheet, prepares the chart and reprotects the sheet, and it only ever
needs to be run once:

Sub PrepareChart()
With Sheets("SheetNames")
.Unprotect Password:="MyPassword"
.Shapes("Chart 109").Locked = False
.ChartObjects("Chart 109").Chart.ProtectSelection _
= True
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password:="MyPassword"
End With
End Sub

Now this code can be run whenever you want to show the chart window,
without having to unprotect the sheet:

Sub ShowChartWindow()
ActiveSheet.ChartObjects("Chart 109").Activate
ActiveChart.ShowWindow = True
End Sub

- Jon
 
Jon,
Thank you again for answereing one of my queries. (This is the same project
as my earlier query you responded to.)

sincerely

Jeff Smith
 
Back
Top