Custom Toolbar in Chart

[Randall]

How do you create a custom toolbar with a ComboBox that will be avaliable
when a chart is active?

I'd like to have a TOOLBAR where a ComboBox (or ListBox) is used to select a
variable. This would then change the source data for the chart. I've got
the code to change the chart, I just can't get a ComboBox in a Toolbar to
work properly when a chart is active.

Tx,
Randall

 

[Tushar Mehta]

I'm not sure what you mean by "I just can't get a ComboBox in a Toolbar
to work properly when a chart is active." If you mean it should be
active only when a chart is...

One 'trick' is to create an event hook that ties to a toolbar button
that XL enables whenever a chart is the active element (if you can find
such a commandbar item). This technique is further explained in
Professional Excel Development by Bullen, Bovey, and Green.

Another approach would be to add to your ComboBox code something along
the lines of
If activechart is nothing then
msgbox "Sorry, please select a chart first"
exit sub
end if

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Randall -

Less complicated than Tushar's suggestion is to use forms menu controls
(which include a listbox and a combobox) to select the variables. Forms
menu controls can be used in a chart sheet or in a worksheet, and they
link to worksheet ranges, so the chart can be dynamically controlled by
these controls without requiring VBA macros to be enabled.

See the Worksheet Controls for Chart Interactivity section of this web
page for a couple simple examples, which can be easily used with the
Dynamic Charts examples also on this page:

http://peltiertech.com/Excel/Charts/Dynamics.html

A more elaborate example is given in this article:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

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