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
.
.