If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Chart menu visible property
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 |
#2
|
|||
|
|||
Chart menu visible property
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").Visibl e .... -- 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 In article , says... 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 |
#3
|
|||
|
|||
Chart menu visible property
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 -----Original Message----- 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").Visib le .... -- 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 In article , says... 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 . |
#4
|
|||
|
|||
Chart menu visible property
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/ _______ Sandy V wrote: 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 -----Original Message----- 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").Visi ble .... -- 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 In article , says... 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 . |
#5
|
|||
|
|||
Chart menu visible property
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 -----Original Message----- 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/ _______ Sandy V wrote: 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 -----Original Message----- 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").Vis ible .... -- 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 In article , says... 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 . . |
#6
|
|||
|
|||
Chart menu visible property
Sandy -
What kind of problems do you suspect? - Jon Sandy V wrote: 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 -----Original Message----- 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/ _______ Sandy V wrote: 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 -----Original Message----- 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").Vi sible .... -- 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 In article , says... 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 . . |
#7
|
|||
|
|||
Chart menu visible property
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 -----Original Message----- Sandy - What kind of problems do you suspect? - Jon Sandy V wrote: 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 -----Original Message----- 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/ _______ Sandy V wrote: 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 -----Original Message----- 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").V isible .... -- 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 In article , says... 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 . . . |
#8
|
|||
|
|||
Chart menu visible property
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/ _______ Sandy V wrote: 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 -----Original Message----- Sandy - What kind of problems do you suspect? - Jon Sandy V wrote: 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 -----Original Message----- 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/ _______ Sandy V wrote: 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 -----Original Message----- 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 In article , says... 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 . . . |
#9
|
|||
|
|||
Chart menu visible property
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/ _______ snip |
Thread Tools | |
Display Modes | |
|
|