Using Excel VBA Combo Box to Draw A Chart From Different Sheets

Question

Okay so what I am trying to do is have a userform with a combobox that displays each sheet as an option. I got that down. I want to be able to select a sheet and draw a chart from certain ranges on said sheet on my userform. I hope that makes sense.... Here is what I have got so far. It works with the first combobox selection, but once I change sheets and try to draw a chart from a different worksheet I get an error, "Method 'Values' of object 'series' failed. Any help would be greatly appreciated. Thanks in advance =)

    Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()

If ComboBox1.Text = "Select A Client" Then
MsgBox "You Must Select a Name to Continue.", , "ERROR:Select A Name"
ComboBox1.SetFocus
    Exit Sub
End If

Dim MyChart As Chart
Dim ChartData As Range
Dim chartIndex As Integer
Dim ChartName As String

chartIndex = ComboBox1.ListIndex

Select Case chartIndex
    Case 1
        Set ChartData = ActiveSheet.Range("L4:L103")
        ChartName = ActiveSheet.Range("A1")

    End Select

Application.ScreenUpdating = False

Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

MyChart.SeriesCollection.NewSeries
MyChart.SeriesCollection(1).Name = ChartName
MyChart.SeriesCollection(1).Values = ChartData
MyChart.SeriesCollection(1).XValues = ActiveSheet.Range("J4:J103")

Dim imageName As String
imageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.gif"

MyChart.Export Filename:=imageName

ActiveSheet.ChartObjects(1).Delete
Application.ScreenUpdating = True
UserForm1.Image1.Picture = LoadPicture(imageName)

    End Sub

    Private Sub UserForm_Click()

    End Sub

    Private Sub UserForm_Initialize()
Dim sht As Worksheet, txt As String
For Each sht In ActiveWorkbook.Sheets
Me.ComboBox1.AddItem sht.Name
Next sht

    End Sub

Ambie Ambie · Accepted Answer · 2015-10-08T01:16:12

The only time you are defining ChartData is if the ComboBox ListIndex is 1. Any other selection and the ChartData object will remain Nothing. Therefore when you try to set your chart values to ChartData you'll receive an error.

It's difficult to tell what you're trying to do with the Ranges. If you want to define the Range on whichever sheet is selected in the ComboBox then you're code would need to look like this:

Dim ws as Worksheet

Set ws = ThisWorkbook.Worksheets(ComboBox1.Text)

And delete your Select block.

All of your code is referencing the ActiveSheet which is probably not what you want, so you would need to replace all references to that with this new variable ws.

Using Excel VBA Combo Box to Draw A Chart From Different Sheets

1 Answers