Chart menu visible property

  • Thread starter Thread starter Sandy V
  • Start date Start date
S

Sandy V

Hi,

On a worksheet there appear to be two independent settings
for -
Application.CommandBars("Chart").Visible

One when no chart is selected, and another when a chart is
active. Typically they would be False and True
respectively.

I would like to return, apply my own settings, then
correctly restore both when done, regardless as to whether
or not a chart is selected, and without having to select /
de-select a chart.

TIA,
Sandy
 
OK, so what am I missing? Why not use a variable for the current value,
change it as desired and restore the setting from the variable used to
store the current setting? You already know the correct syntax for the
property. To get you started, consider the untested

Dim SavedState as Boolean
SavedState=Application.CommandBars("Chart").Visible
....

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004
 
Hi Tushar,

First of all thanks for replying.

The trouble is the chart menu visible property can change
from true / false simply by selecting / deselecting a
chart (depending on user settings). And this may occur
while my code is running. I can only return the visible
property at any one time. So the problem is how to detect
the original combination of settings, and eventually re-
apply same.

I think settings could be any of these combinations

No Chart selected | Chart selected
False | False
False | True
True | True
but not
True | False

Consider this scenario:
Original setup is False | True
Chart selected, code starts, menu visible so
SavedState = True
Chart deselected, menu not visible
Code about to terminate, apply SavedState (true)

But this is not correct, if no chart is selected the
correct setting for the menu should be visible = False

One solution would be to select / deselect a chart at an
early stage of code and detect the combination, then do
similar when done. But I prefer not to do this.

I could be missing something simple, hope I am!

Regards,
Sandy
 
Sandy -

Are you messing with the visible property of the chart command bar? If
not, even if it appears and disappears during the macro, it will retain
its behavior.

The chart command bar wants to stay invisible when no chart is active,
and visible when a chart is active. If you set the visible property to a
state it doesn't like, it will stay in that condition whether or not a
chart is selected. If the visible property is already in a state it
doesn't like, changing the setting will revert it to its happy changing
state. There seems to be a third setting besides True and False for the
..Visible property: Maybe. (xlMaybe? xlWhatever.)

Since that doesn't seem very clear, I'll give an example. If the chart
is visible and so is the chart menu, making the chart menu invisible
will mean it stays hidden no matter what you select. If you select the
chart, then make the chart command bar visible, it reverts to the
witching behavior. If you then unselect the chart, the chart command bar
goes away. Make it visible now and it will stay visible.

All of the command bars that pop in and out based on the selection
function in the same manner.

But the best you can do is not change the setting at all.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,

Yes I can relate to all of what you say, and thanks for
the detailed description.

Unfortunately I do want to change the combination of
visible settings for the Chart menu. I don't mind if it is
visible or not, but I do not want it changing while my
code is running. With the False | True combination this
occurs as a chart is selected or deselected.

Reason, I am highly suspicicious that the changing state,
together with other things going on with my code, causes
problems in rare situations (only with xl97). Without a
solution to detecting and applying the "combination", as
seems tbe case, I will simply disable the toolbar.

Don't suppose you have the constant values for xlMaybe &
xlWhatever, would be very useful, also xlWhenIfeelLikeIt !

Many thanks again,
Sandy
 
Hi Jon,

I think the problem(s) relate to redrawing chart after a
format change to a chart item, in particular colour.
Having said that, the chart appears to redraw perfectly,
but later when another chart item is selected the entire
chart object can apparently disappear leaving only
selection handles.

But move something over the chart and it is "repainted"
under the moving object (eg a msgbox or by scrolling). In
worst case XL can crash, even before a class event I have
created to trap selection is activated. This is not
related to screenupdating or enableevents (although I've
tried reversing these to in various places, also
experimented with DoEvents).

A more detailed description would be far too long to place
here. However disabling the chart menu seems to reduce
occurrence, which in any case is confined to certain
charts (I don't mean "types") and after a particular chain
of actions (xl97 only).

If this means anything to you I'm all ears !

Regards,
Sandy
 
Sandy -

It means you've figured out a workaround that someone who merely
followed help and the object model wouldn't have found. I guess the
trick is to determine the visible state of the charts command bar with
and without a chart selected, noting this, then resetting it as
appropriate at the end. I think I described "appropriate" in an earlier
post.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,

I've noticed that charts in xl97 are, how can I put
it, "sensitive" and sometimes require a little extra
attention (maybe SP1 more so than SP2).

Regards,
Sandy
-----Original Message-----
Sandy -

It means you've figured out a workaround that someone who
merely followed help and the object model wouldn't have
found. I guess the trick is to determine the visible
state of the charts command bar with and without a chart
selected, noting this, then resetting it as appropriate
at the end. I think I described "appropriate" in an
earlier post.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Click to expand...
<snip>
 
Back
Top