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


-----Original Message-----
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


.

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


"Greg Wilson" wrote in message
...
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


-----Original Message-----
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


.

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

-----Original Message-----
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


"Greg Wilson" wrote
in message
...
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


-----Original Message-----
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


.



.

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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

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



"Jon Peltier" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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

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


-----Original Message-----
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



"Jon Peltier" wrote in
message
...
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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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





.

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


"Greg Wilson" wrote in message
...
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


-----Original Message-----
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



"Jon Peltier" wrote in
message
...
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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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





.

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


-----Original Message-----
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


"Greg Wilson" wrote
in message
...
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


-----Original Message-----
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



"Jon Peltier" wrote in
message
...
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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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





.



.

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 Wilson" wrote in message
...
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


-----Original Message-----
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


"Greg Wilson" wrote
in message
...
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


-----Original Message-----
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



"Jon Peltier" wrote in
message
...
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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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





.



.

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



"Greg Wilson" wrote in message
...
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


-----Original Message-----
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


"Greg Wilson" wrote
in message
...
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


-----Original Message-----
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



"Jon Peltier" wrote in
message
...
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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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





.



.

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 Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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


"Greg Wilson" wrote in message
...

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



-----Original Message-----
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



"Jon Peltier" wrote in

message

...

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:

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




.

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


"Jon Peltier" wrote in message
...
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 Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:
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


"Greg Wilson" wrote in message
...

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



-----Original Message-----
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



"Jon Peltier" wrote in

message

...

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jeff Smith wrote:

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




.