0

I'm querying for a chart in the worksheet and I can see it's there but everytime I execute the following piece of code it evaluates to 'Nothing'. Why is that?

Sub DeleteGraph()
Dim graph As ChartObject

On Error Resume Next
'graph always evaluates to Nothing
graph = ActiveSheet.ChartObjects("frontier").Activate

    If Not (graph Is Nothing) Then 'graph always evaluates to Nothing
        ActiveSheet.ChartObjects("frontier").Activate
        ActiveChart.Parent.Delete
    End If

End Sub

This macro executes when a button is clicked on the worksheet that runs the following Initialize macro that resets all calculations and erases any graph if it exists:

Sub InitializeSheet()
    'Get number of projects on worksheet and the corresponding 'final' cell number/ref to use in calculations - Private/Module Variables
    projectCount = Application.WorksheetFunction.Count(Range("A:A"))
    lastCell = projectCount + 1
    DeleteGraph 'Above Macro called here
    'Clear previous calculation data
    Range("I:AD").Value = ""


End Sub

I need to delete the graph created by the macro when resetting/initializing the sheet. I check if the graph exists (and the user hasn't deleted it manually) before performing the deletion. For some reason I can't get this to work. What am I overlooking?

1

Beware using On Error Resume Next in your code, especially if it's code that hasn't been tested thoroughly. In this case, it's masking a syntax error in the line

graph = ActiveSheet.ChartObjects("frontier").Activate

hence, no value is ever assigned to graph. Try this instead:

Sub DeleteGraph()
Dim graph As ChartObject

On Error GoTo ErrHandler
Set graph = ActiveSheet.ChartObjects("frontier")
graph.Delete
Exit Sub

ErrHandler:
msgBox "Chart does not exist on active sheet!"
Exit Sub

End Sub

If you don't like the pop-up warning when the chart is not found, just remove the msgBox line.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.