CODEWITHSUNDEEP
excelvbacharts
user3417982
user3417982

Reputation: 1

VBA Excel chart in User form error - Compiler Error- Argument not optional

I'm trying to write a program that gets the values from an excel spreadsheet and puts them into a chart on a userform. I'm using Excel 2011, and I've downloaded a copy of Microsoft OWC 10.0 to allow the addition of a chart. My code is below:

Private Sub UserForm_Initialize()

Dim M As Integer Dim N As Integer

With UserForm1.ChartSpace1

M = .Charts.Count
If M = 0 Then GoTo COMEHERE Else GoTo Finish

COMEHERE: .Charts.Add With .Charts(0) ' Create a bar chart. .Type = chChartTypeXYScatterLine

           ' Add data series to the chart.

            N = .SeriesCollection.Count
            If N = 0 Then GoTo COMEHERE1 Else GoTo Finish

COMEHERE1:

            .SeriesCollection.Add
        'Worksheets("Sheet1").Range("D1").Value = 1 ' test to check how far program gets


        ' Set the properties of the first data series.
          With .SeriesCollection(0)
            .SetData chDimSeriesNames = Worksheets("Sheet1").Range("A1").Value
            .SetData chDimXValues = Worksheets("Sheet1").Range("A3:A12").Value
            .SetData chDimYValues = Worksheets("Sheet1").Range("B3:B12").Value

           End With
           .HasLegend = True

Finish:

        End With

End With End Sub

The M and N integers are a hangover from originally writing the code as private sub ChartSpace1_Click(), where I'd get a new chart and series every time I clicked the mouse. The values in columns A3:A12 and B3:B12 are simply 1 to 10

When the code gets to the .setData function, it falls over (Compiler Error - Argument not optional). I've seen similar examples of this code online where people have apparently gotten around this, but I couldn't figure it out or get their code to work.

The purpose for this code is to go into a larger program that I'm writing to show various scientific graphs based on user inputs.

ANy help that people have would be greatly appreciated

J

Upvotes: 0

Views: 618

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6063

You may find this approach to be much easier: Displaying A Chart In A Userform. No need for OWC or other added libraries. It's written for a Windows version of Excel, but you can probably figure out how to make it work on a Mac.

Upvotes: 0

Related Questions