Code to add Chart not working

[mari_hitz]

Hi Everybody!

I have a code that used to work just fine and now for some reason it stopped working. The code is really helpful and what it does is: you define an specific range of information and the code adds a chart. Now, creates the workbook and the sheet for the chart but the chart itself is not created. Do you know why could this be happening? I am running Microsoft Excel 2007

The code is the following:

Sub CommandButton1_Click()
    Dim oRangeSelected As Range
    
    Dim objExcel As Object
    Dim objWorkbook As Workbook
    Dim objWorksheet As Worksheet
    Dim objRange As Range

    Dim objChart As Chart
    
    Dim SeriesCount As Integer
    Dim i As Integer

    On Error Resume Next
    Set oRangeSelected = Application.InputBox( _
                Prompt:="Please select a range of cells!",Title:="Select a Range", _
                Type:=8)
    On Error GoTo 0
    
    If oRangeSelected Is Nothing Then
        MsgBox "You are going to cancel this action. Are you Ok?"
        Exit Sub
    Else
        MsgBox "You selected: " & oRangeSelected.Address(External:=True)
    End If
                                                                            
    Set objExcel = CreateObject
objExcel.Visible = True
    
    Set objWorkbook = objExcel.Workbooks.Add()
    Set objWorksheet = objWorkbook.Worksheets(1)

    oRangeSelected.Copy
    objWorksheet.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
    
    Set objRange = objWorksheet.UsedRange
    
    objRange.Select

    Set objChart = objExcel.Charts.Add
    
    With objChart
        SeriesCount = .SeriesCollection.Count
 .Activate
        .ChartType = 65
        
             
        For i = 1 To SeriesCount
            .SeriesCollection .Border.Weight = -4138
        Next i
        
        .SeriesCollection(1).Border.ColorIndex = 0
        .SeriesCollection(1).MarkerBackgroundColorIndex = 0
        
        If SeriesCount > 1 Then
            For i = 2 To SeriesCount
                .SeriesCollection .Mark
Next i
        End If
        
        .HasTitle = True
        .ChartTitle.Font.Size = 18
        .ChartArea.Fill.Visible = True
        .ChartArea.Fill.PresetTextured 15
        
        .ChartArea.Border.LineStyle = 1
        
        .HasLegend = True
        .Legend.Shadow = True
        
        '// Switch Row/Columns to rows
        .PlotBy = xlRows

    End With '// objChart
End Sub

 

[Jon Peltier]

This isn't necessarily related to your problem, but I don't get this code:

    Set objExcel = CreateObject
objExcel.Visible = True
    
    Set objWorkbook = objExcel.Workbooks.Add()

First, CreateObject would need some parameters. Second but more important, I don't know why you need to create a new Excel instance. You should be able to replace this bit of code with:

Set objWorkbook = Application.Workbooks.Add

Does the worksheet have any data pasted into it? Copying data in one instance of Excel does not allow paste special in another instance. You may be lacking any data in the chart because it is created with no data selected.

 

[mari_hitz]

Hi Jon,

Thanks for your reply! I forgot to mention that the data I am selecting is from a pivot table. Could this be the reason of the issue?

 

[Jon Peltier]

I thought I'd responded already that it shouldn't matter if the data is from a pivot table, but my post must have been eaten by the browser.

If the pivot table does not contain plottable data, then the chart will not come out right, but this is also true if a regular range had no plottable data.