Chart in Userform with Listbox Control

Illustrates how to use a listbox to control the order of how charts appear in an image control.

In Displaying a Chart in a Userform, John Walkenbach describes how to use VBA to save an embedded chart as a GIF file and then load that file into a userform. The userform contains controls that allow you to scroll forward or backward through all of the charts that are available.

I recently built a model where I needed to to take this example another step further by building in functionality to allow the user to:

• See a list of the names of all charts available to be displayed.
• Allow the user to pick what chart he or she wanted to see based on that list.

To do so, the use of a listbox in a userform seemed like the ideal setup to use.

As an example, start with a spreadsheet containing four embedded charts:

The charts are located on a sheet named "Charts". All of the charts are aligned to the worksheet grid and sized exactly the same. To align and size the charts, I used the following macro:

Sub AlignCharts()
    Application.ScreenUpdating = False
    Dim ChtObj As ChartObject
    For Each ChtObj In Sheets("Charts").ChartObjects
        ChtObj.Top = ChtObj.TopLeftCell.Top
        ChtObj.Left = ChtObj.TopLeftCell.Left
        ChtObj.Height = 126
        ChtObj.Width = 192
    Next ChtObj
End Sub

A name is applied to each chart. Naming the chart is the key to allowing you to control the order in which the chart appears in the listbox. You can use any name that you want. In this example the first chart is named "Cht01", the second "Cht02", the third "Cht03", and the fourth "Cht04". To name each chart, I used the following macro:

Sub NameChart()
    ActiveChart.Parent.Name = "ColumnCht"
End Sub

Note that to run the "NameChart" macro, you must activate the chart (click on it) first and then run the macro.

At this point it's time to build the userform. As in the Spreadsheet Page example, the userform contains an image control and buttons that allow the user to scroll forward or backward through the charts. I've added the additional listbox control:

When you click on the "Chart Userform" button which is embedded in the "Charts" sheet, the userform is shown via the following code. Note that the button embedded in the "Charts" sheet is named "CB1", the userform is named "userform1", and the listbox is named "listbox1". The code is placed in the "Charts" sheet and the charts are ordered high-to-low in any order that you want the user to see.

Private Sub CB1_Click()
'   Clear the RowSource property
    UserForm1.ListBox1.RowSource = ""

'   Add items to ListBox
    UserForm1.ListBox1.AddItem "Column Chart"
    UserForm1.ListBox1.AddItem "Line Chart"
    UserForm1.ListBox1.AddItem "Area Chart"
    UserForm1.ListBox1.AddItem "Bar Chart"

    UserForm1.Show vbModeless
End Sub

After the userform is shown via the code above, it is then initialized to show the first chart in the image control via the code below. A textbox (named "TB1") located above the image control contains the chart title.

Public Rng As Range
Public Cht As String

Private Sub UserForm_Initialize()
    TB1 = "Column Chart"
    Cht = "Cht01"
    Call UpdateChart
End Sub

Private Sub UpdateChart()

    Set CurrentChart = Sheets("Charts").ChartObjects(Cht).Chart

    '   Saves the chart as GIF file
    Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
    CurrentChart.Export FileName:=Fname, FilterName:="GIF"

    '   Shows the chart in the image control
    Image1.Picture = LoadPicture(Fname)

End Sub

Finally, a listbox click event allows the charts to appear in the image control when the listbox is clicked.

Private Sub Listbox1_Click()
    If ListBox1.ListIndex = 0 Then
        TB1 = "Column Chart"
        Cht = "Cht01"
    ElseIf ListBox1.ListIndex = 1 Then
        TB1 = "Line Chart"
        Cht = "Cht02"
    ElseIf ListBox1.ListIndex = 2 Then
        TB1 = "Area Chart"
        Cht = "Cht03"
    ElseIf ListBox1.ListIndex = 3 Then
        TB1 = "Bar Chart"
        Cht = "Cht04"
    End If
    Call UpdateChart
End Sub

Download the Example File

Note: the technique of loading a chart into a userform is credited to John Walkenbach. His example of Displaying a Chart in a Userform can be found at The Spreadsheet Page.