|
|
Peltier Tech
Books at Amazon.com
Buy me a coffee If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee. |
Select a Chart Type Using a List Box or Drop Down.
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.Custom Solutions | Commercial Utilities | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2009. All rights reserved. |
