Select a Chart Type Using a List Box or Drop Down.

Excel Dashboards by ExcelUser.com - Instructional eBook and Ready-to-Use Workbooks.

Select a Chart Type Using a List Box or Drop Down

Two approaches for changing a chart type using list box or drop down type controls.

Here are two approaches to using a listbox to select a chart style. These assume an embedded chart on a worksheet, which allows you to use either a forms toolbar list box or a controls toolbar listbox. For a chart located on its own chart sheet, you can only use a forms toolbar listbox.

In approach 1, I have a list of vba's integer value for each type of chart (e.g., xlColumnClustered = 51) in a range next to the listbox source range. Then I can use index/match to put the selected xlChartType value in its own cell. The macro (forms toolbar) or event procedure (controls toolbar) references this integer value, and executes this command:

ActiveSheet.ChartObjects(1).Chart.ChartType = myChartType

Very nice. Then I remembered that I don't format all chart types the same way, and I may want different chart types to have different chart and axis titles. Too much coding for lazy old me. So Approach 2 requires me to build and format all the relevant charts beforehand, and stack them on top of each other. My listbox now refers to the chart name or index ("Chart 1" or 1). This line does the rest:

ActiveSheet.ChartObjects(.Range("myChartName").Value).BringToFront

The example file shows both ways to do this, using both types of controls. I used workbook range names to define where the source ranges and linked cells are; one worksheet has a handy list of these names for your reference.

Peltier Technical Services, Inc.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile