Display 12 Months Charts in one Chart Object

I know, we need some details on this to start with.  Since, this is all about Graph Charts I will present some data for our chart below then go into details about the whole project.  Here is the image of sample Chart prepared using the sample data shown above the Chart:

The sample data is the summary of Revenue, Expenses detail statements located elsewhere on the Worksheet and not shown. The statement includes data of three Branch locations of a Company for the month of January.

When Graph Charts are prepared this way, our Workbook will have twelve Worksheets and twelve Graph Charts for the months from January to December.

When your Boss wants to review the Summary Data/Graph Chart of any month he has to select the Worksheet of that particular month to view the data or Graph Chart.  He/She has to move from one worksheet to the other to view the information/chart from different worksheets.

But, with a cool trick we can make this task very easy and show the Summary statement of any month and it's corresponding Graph Chart on particular location on a worksheet, based on the month selected from a Listbox. Besides that you don't need to prepare Graph Charts on all twelve month's Worksheets. But the Summary data must be present on the same Range location on all twelve worksheets, for JAN to DEC.

For example, the summary statement shown for the month of January in the above image is placed within the Cell Range L10:O13 on JAN worksheet. The actual Revenue, Expenses and Profit/Loss Values are within the Cell Range M11:O13. Summary statement of all twelve months must be maintained on the same Range Address on all Worksheets of JAN to DEC.

Image of our Planned Worksheet is given below:

Check the Worksheet Tabs at the bottom of the image, you will find that we have added two more Worksheets with the name 'Control' and 'Ctrl_data' besides worksheets of JAN to JUN. The Control Worksheet holds sample Summary data in Range A1:D4. The Graph Chart shown there is prepared from the sample data. The Listbox at the left side of the Chart holds the names of the month, or the Worksheet names that holds the summary data for the month of JAN to DEC. When the user selects a month (say MAR) from the listbox a Macro connected to the Listbox runs and copies the summary data from the selected Worksheet Summary data Range: M11:O13 and paste it into the Control Worksheet sample summary data area: A1:D4. The Graph Chart automatically reflects the change immediately. The user can select any month of his choice from the list and its corresponding data will be brought in to the Control worksheet and the Graph will change accordingly. So your Boss can stay on the Control worksheet and view the Summary Statement and Graph Charts of any month selected by him/her from the Listbox.

Now, let us get to work to build our trick. We assume that you have Excel2007, while referring to Menus and Options.

1. Open a new Workbook and save it with a name and in Macro enabled format (with .xlsm Extension). If you are using Excel2003 then you may save it as you do normally.
2. Right-click on Sheet1 and select Rename from the Shortcut Menu and change the name of the worksheet as JAN.
3. Rename the other two Worksheets as FEB and MAR.
4. Select JAN worksheet and create sample Graph Chart data, as shown on the first image above, and within the same Cell Range: L10:O13 .   Don’t create any Graph Chart there.
5. Copy the data from JAN worksheet and Paste it on FEB and MAR worksheets on the same Cell Range.  Make some changes in Revenue, Expenses figures on FEB and MAR worksheets.  Profit/Loss Value is the difference between Revenue and Expenses.

   Now, we need two more Worksheets.

6. Insert a new worksheet and rename it as Ctrl_Data.
7. Type the information on the Ctrl_Data worksheet as shown in the image below. 

We need to create one more Worksheet with the name Control. The worksheet image is the second one from top.  Before going into that let us check the Ctrl_Data worksheet contents.

• Cell A1 holds the value selected from the Listbox and it is for System use only.
• Cell Range A3:A5 holds the source data for the Listbox.  This can be expanded up to the month DEC, when you add more sheets to your workbook.
• Cell A16 holds the common Range Address of JAN to DEC worksheets, where the Revenue, Expenses & Profit/Loss Values are stored.

1. Insert a new worksheet and rename it as Control.
2. Copy the sample data from Range L10:O13 of JAN worksheet and paste it in Cell A1 on Control worksheet.
3. Click on the Insert drop-down control in the Controls group under Developer Menu.
4. Click the Listbox Tool to select it from the ActiveX Controls group, rather than from the Form Controls group.
5. Draw a Listbox somewhere below the sample data on the Control worksheet.  Check the Listbox shape on sample Control worksheet image above (second from top).
6. While the Listbox is in selected state click on the Properties button in the Controls group under the Developer Menu.  Check the Control worksheet image below with the Listbox’s Properties Sheet displayed.
   

   We will be making changes to four different property values on the Property Sheet.

7. Check the Name Property Value, if it is not set as ListBox1 then change it to read as ListBox1.
8. Locate the Property LinkedCell and type: Ctrl_Data!A1 at the right column.
9. Type: Ctrl_Data!A3:A14 for the ListFillRange property.  When you update this property you can see the Listbox control is filled with the month names: JAN, FEB & MAR.
10. Check whether the MultiSelect Property is set with the value: 0 – frmMultiSelectSingle or not, if not then set it as 0 – frmMultiSelectSingle from the Drop-down box.
11. Click on the Close (X) button (top right corner of the property sheet) to close the property sheet.
12. While the Listbox is still in selected state, click on the View Code button, near the Properties button, to open the VBA Code Window with the empty ListBox1_Click()  Event Procedure’s starting and ending lines.
13. Copy and paste the Macro Code given below over-writing the existing ListBox1_Click() Event Procedure:

    Microsoft Excel2007 Version Code:

    Private Sub ListBox1_Click()
    Dim m_mth, m_Range
    On Error GoTo ListBox1_Click_Err
    m_mth = Worksheets("Ctrl_Data").Range("A1").Value
    m_Range = Worksheets("Ctrl_Data").Range("A16").Value
    'if clicked on an empty area in the list box, then exit
    If Len(m_mth) = 0 Then
      Exit Sub
    End If
    'Insert selected month Name in the Chart Data area left corner cell
    Worksheets("Control").Range("A1").Value = m_mth
    'Copy the cell Source Range contents
    Sheets(m_mth).Range(m_Range).Copy
    Sheets("Control").Activate
       'Paste copied values into the Control Worksheet Range
       'Starting with Cell B2
       ActiveSheet.Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      'Set reference to the Chart Object on Control Worksheet
      With Worksheets("Control").ChartObjects(1).Chart
        .HasTitle = True
        .ChartTitle.Text = "Profit/Loss - " & m_mth & "/" & Year(Now())
      End With
    ListBox1_Click_Exit:
    Exit Sub
    ListBox1_Click_Err:
    MsgBox Err.Description, , "ListBox1_Click()"
    Resume ListBox1_Click_Exit
    End Sub

    Microsoft Excel97-2003 Code:

    Private Sub ListBox1_Click()
    '---------------------------------------------------------
    'Author : a.p.r.pillai
    'Date   : May 2012
    'Remarks: All Right(c) Reserved by excel.msaccesstips.com
    '---------------------------------------------------------
    Dim m_mth, m_Range
    On Error GoTo ListBox1_Click_Err
    m_mth = Worksheets("Ctrl_Data").Range("A1").Value
    m_Range = Worksheets("Ctrl_Data").Range("A16").Value
    'if clicked on an empty area in the list box, then exit
    If Len(m_mth) = 0 Then
      Exit Sub
    End If
    'Insert selected month Name in the Chart Data area left corner cell
    Worksheets("Control").Range("A1").Value = m_mth
    'Copy the Source Cell Range contents
    Application.CutCopyMode = False
    Sheets(m_mth).Range(m_Range).Copy
    Sheets("Control").Activate
    'Paste copied values into the Control Worksheet Range
    'Starting with Cell B2
       ActiveSheet.Range("B2").Select
       ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
      'Set reference to the Chart Object on Control Worksheet
      With Worksheets("Control").ChartObjects(1).Chart
        .HasTitle = True
        .ChartTitle.Text = "Profit/Loss - " & m_mth & "/" & Year(Now())
      End With
    ListBox1_Click_Exit:
    Exit Sub
    ListBox1_Click_Err:
    MsgBox Err.Description, , "ListBox1_Click()"
    Resume ListBox1_Click_Exit
    End Sub

    Now we must create the Graph Chart using the sample data on the Control worksheet.

14. Highlight Cell Range A1:D4.
15. Click on the Column drop-down control, from the Charts group of Insert Menu, and select the first Column chart type.
16. Draw a Graph Chart, like the sample Chart shown on the second image on the top, at the right side of the Listbox. 
17. If Chart Title is not shown on the top area of the Graph Chart then select the first Chart Layouts option under Design Menu.
18. Right-click on the first Data Series Bar.
19. Select the Add Data Labels option from the displayed Shortcut Menu.
20. Set data labels for the other two set of Bars too.
21. Save the Workbook.
22. Select the Developer Menu and click on the Design Mode button to de-select it.

Now, it is time to try out our Project.

Click on FEB in the Listbox control.  If everything about the design tasks went through well then you should see the Graph Chart Data is copied from the FEB worksheet to the Control worksheet summary data Range: B2:D4 and the Graph chart displays information for the month of February.  The Graph Chart Title also changes automatically.

Click on JAN or MAR in the Listbox and watch the selected month’s summary data and Graph Chart appears on the Control Worksheet.

Tip:  You may add worksheets for APR to DEC with sample data and update the Listbox contents on the Ctrl_Data worksheet to try out the program for all twelve months.

• Welcome/Good-Bye Messages
• Displaying worksheet Formula
• Microsoft Excel Automation