Macro generates chart but continously and I want once

[#1]

i have created the macro to generate the bar chart but its generating continously and i want only once .. plz do the needful.

macro code:

Option Explicit

Sub GenerateBarChart()

Dim myChart

' Create a new Bar Chart

Set myChart = _

ActiveDocument.ActiveSheet().CreateBarChart()

' Add a dimension of Country to the new chart

myChart.AddDimension "COUNTRY"

' Add an expression

myChart.AddExpression "Sum(SALES) "

' Get the properties object

Dim cp

Set cp = myChart.GetProperties()

' Set the title of the dimension

cp.Dimensions(0).Title.v = "COUNTRY Name"

' Set the Title-in-chart text

cp.ChartProperties.Title.Title.v = "SALES by COUNTRY"

' Set the Window title

cp.GraphLayout.WindowTitle.v = "SALES $ by COUNTRY"

' Set sort by Y-Value

cp.SortByYValue = -1

' Get the expression properties

Dim expr, exprvis

Set expr = _

cp.Expressions.Item(0).Item(0).Data.ExpressionData

Set exprvis = _

cp.Expressions.Item(0).Item(0).Data.ExpressionVisual

' Set the Expression label

exprvis.Label.v = "SALES $"

' Set the "Values on Data Point" option

exprvis.NumbersOnBars = -1

' Set the number format for the expression

exprvis.NumberPresentation.Dec = "."

exprvis.NumberPresentation.Fmt = "#,##0.00"

exprvis.NumberPresentation.nDec = 2

exprvis.NumberPresentation.Thou = ","

exprvis.NumberPresentation.Type = 11 'fixed

exprvis.NumberPresentation.UseThou = 1

' Apply the modified properties

myChart.SetProperties cp

End Sub

Message was edited by: Onno van Knotsenburg Added a much more useful title (compared with "hii")

[tamilarasu · Accepted solution]

Hi Abhishek,

First, you have store the created bar chart id into somewhere (Variable). Later, you can check whether a bar chart ID (created by macro) is exists or not. I have created an another sub routine which checks the whether already created bar chart id in the sheet. If yes, you will get a pop up message as "Bar chart already exists. Delete the existing one to create a new bar chart..".  If no, new bar chart will be created. See below code for better understanding!!

Sub BarChart

' Check whether stored bar chart id exists in the sheet
set v = ActiveDocument.GetVariable("vVar1")
set s=ActiveDocument.ActiveSheet
for i=0 to s.NoOfSheetObjects-1
   if s.SheetObjects(i).GetObjectId =  v.GetContent.String then
   Msgbox "Bar chart already exists. Delete the existing one to create a new bar chart.."
   Exit Sub
   End if   
next
   Call  GenerateBarChart

End Sub

'======================================================================

Sub GenerateBarChart

Dim myChart
' Create a new Bar Chart
Set myChart = _
ActiveDocument.ActiveSheet().CreateBarChart()
' Reset the variable
set v = ActiveDocument.GetVariable("vVar1")
v.SetContent myChart.GetobjectId , true

' Add a dimension of Country to the new chart
myChart.AddDimension "COUNTRY"
' Add an expression
myChart.AddExpression "Sum(SALES) "
' Get the properties object

Dim cp
Set cp = myChart.GetProperties()
' Set the title of the dimension
cp.Dimensions(0).Title.v = "COUNTRY Name"
' Set the Title-in-chart text
cp.ChartProperties.Title.Title.v = "SALES by COUNTRY"
' Set the Window title
cp.GraphLayout.WindowTitle.v = "SALES $ by COUNTRY"
' Set sort by Y-Value
cp.SortByYValue = -1
' Get the expression properties
Dim expr, exprvis
Set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData
Set exprvis = cp.Expressions.Item(0).Item(0).Data.ExpressionVisual
' Set the Expression label
exprvis.Label.v = "SALES $"
' Set the "Values on Data Point" option
exprvis.NumbersOnBars = -1
' Set the number format for the expression
exprvis.NumberPresentation.Dec = "."
exprvis.NumberPresentation.Fmt = "#,##0.00"
exprvis.NumberPresentation.nDec = 2
exprvis.NumberPresentation.Thou = ","
exprvis.NumberPresentation.Type = 11 'fixed
exprvis.NumberPresentation.UseThou = 1
' Apply the modified properties
myChart.SetProperties cp
End Sub

I have attached a sample file. If you have any issues, let me know.

[#3]

Thanku bro.