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

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.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. 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