CODEWITHSUNDEEP
excelvba
user3470049
user3470049

Reputation: 447

How to check if chart was already created

I'm adding a chart via vba with the following commands:

Dim CsvVisualization As ChartObject
Set ChartSizePosition = Range("B8:I25")
Set CsvVisualization = ThisWorkbook.Sheets("Übersicht").ChartObjects.Add(ChartSizePosition.Left, ChartSizePosition.Top, ChartSizePosition.Width, ChartSizePosition.Height)
CsvVisualization.Name = "MeasurementVisualization"
With CsvVisualization.Chart
    .ChartType = xlXYScatterSmoothNoMarkers
...

How could I proofe if this chart was already created when the excel file is reopened? I tried something like:

If CsvVisualization Is Nothing Then

But CsvVisualization won't hold it's value when the file is closed and opened again.

Thank you for help!

Upvotes: 5

Views: 21101

Answers (3)

Federico Sanchez
Federico Sanchez

Reputation: 145

You can try if you would get an error by referencing the object 

On Error Resume Next
Set CurrentChart = thisworkbook.sheets("Übersicht").ChartObjects("MeasurementVisualization")
On Error GoTo 0

If IsEmpty(CurrentChart) Then
MsgBox "Not found"
Else
CurrentChart.Activate
End If

you can replace my codes inside the If with creating the chart....

Upvotes: 0

FreeMan
FreeMan

Reputation: 5687

Dim CsvVisualization As ChartObject
Dim MyChartName as string
Dim CreateChart as Boolean

MyChartName = "MeasurementVisualization"
CreateChart = True

If ThisWorkbook.Sheets("Übersicht").ChartObjects.Count = 0 Then
  For Each CsvVisualization in ThisWorkbook.Sheets("Übersicht").ChartObjects
    If CsvVisualization.name = MyChartName then
      CreateChart = False
    End If
  Next
End if

If CreateChart then
  Set ChartSizePosition = Range("B8:I25")
  Set CsvVisualization = ThisWorkbook.Sheets("Übersicht").ChartObjects.Add(ChartSizePosition.Left, ChartSizePosition.Top, ChartSizePosition.Width, ChartSizePosition.Height)
CsvVisualization.Name = "MeasurementVisualization"
  With CsvVisualization.Chart
    .ChartType = xlXYScatterSmoothNoMarkers
...
End If

Upvotes: 6

JNevill
JNevill

Reputation: 50019

You could look at all the charts in the workbook to see if a chart with that name already exists when the sheet opens with:

Private Sub Workbook_Open()
    Dim testChart As ChartObject

    For Each testChart In ThisWorkbook.Charts
        If testChart.Name = "MeasurementVisualization" Then
            'Chart exists, so do something or not or whatever or what have you
        End If
    Next
End Sub

Upvotes: 5

Related Questions