Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Charts Common Problems > Changing the Series with a Combo Box< Previous | Next > 
 

Step 1 - Change the Chart using a ComboBox on the Chart Area

 
 

This example shows you how you can change the source data of a chart when you select a different series from a drop-down box.

 
 

By selecting a different item in the drop-down you can quickly display the corresponding series of data on the chart.

 
 

We are going to use the ComboBox_Change event from a drop-down listbox to detect when a different series has been chosen.

 
   

 

Step 2 - Things to Look Out For

 
 

  • If you select the chart (and not the combo box) the combobox disappears behind the chart temporarily.

    		 


     

    Step 3 - Add the ComboBox Control

    		 

       

     

     Combo Box - Allows the user to either select an item from the drop-down list or to enter a different value into the text box.

    		 


     

    Step 4 - Populate the ComboBox

    		 

     
    1
    2
    3
    4
    5
    6
    Private Sub Worksheet_Activate()
    Dim irowno As Integer
       For irowno = 3 To 9
          ActiveSheet.ComboBox1.AddItem Range("B" & irowno).Value
       Next irowno
    End Sub
    		   


     

    Step 5 - ComboBox1_Change Event

    		 

     
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Private Sub ComboBox1_Change()
    Dim irowno As Integer
    Dim rgeData As Range

       irowno = ActiveSheet.ComboBox1.ListIndex + 3
       Set rgeData = Range(Cells(irowno, 3), Cells(irowno, 6))
       ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = rgeData
       ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = Range("C2:F2")
    End Sub
    		   


     

    Step 6 - Line by Line

    		 



     

    Step 7 - Using the Forms Toolbar

    		 
     

    Excel does not allow you to add controls from the Control Toolbox Toolbar to a chart sheet.

    		 
     

    If you want to add a drop-down list box to a chart which is displayed on a separate chart sheet you must use the Forms toolbar.

    		 
       
     

    The code is almost identical except that we use a DropDown_Change event instead.

    		 
     

     Combo Box - Allows the user to either select an item from the drop-down list or to enter a different value into the text box.

    		 

     
    16
    17
    18
    19
    20
    21
    Private DropDown1_Change
    Dim irowno As Integer
       irowno = Charts(1).DropDowns(1).Value + 3

    'use the same code as above
    End Sub
    		   


     Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >