My code chart is looping

When you create the chart with Charts.Add, XL creates a chartsheet.
With the Location statement, the chart is moved from its own sheet to
the specified worksheet. This activates the Worksheet, which triggers
the code in Worksheet_Activate all over again.

One way around it is to create the chart with code such as:

Sub myChart()
If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub
ActiveSheet.ChartObjects.Add _
ActiveCell.Left, ActiveCell.Top, 300, 200
With ActiveSheet.ChartObjects(ActiveSheet.ChartObjects. Count).Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=ActiveSheet.Range("A1:A3")
End With
'...Rest of code...
End Sub

Another way around it is to use something like:

Private Sub Worksheet_Activate()
Application.EnableEvents = False
On Error GoTo errHandler
myChart
errHandler:
Application.EnableEvents = False
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello,

I would like to have a council of your share. I used the Macro mode to
create a graph. I inserted this macro in the page of code of my sheet1.
I inserted the name of my procedure in Worksheet_Activate and as soon
as I click on my sheet1, then my procedure this regenerate constantly.
My procedure is carried out in loop and I do not manage to stop it.

How to make for to execute that one alone time at the time of the
opening of my sheet in my sorter?.

In addition, it is possible to remove all the graph during the closing
of an Excel file. I know Workbook_Open but there is not Workbook_Close.
Is what there is a technique?.

Thanks for your help

This is my code:

Sub WorkSheet_Activate()
MyChart
End Sub

Sub MyChart()
Charts.Add
ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.SetSourceData Source:=Sheets("
Menue").Range( _
"A12:B13,A26:B27"), PlotBy:=xlRows
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Menue"
With ActiveChart
Parent.Name = "TheParc"
HasTitle = True
ChartTitle.Characters.Text = "Les Parc"
Axes(xlCategory).HasTitle = False
Axes(xlSeries).HasTitle = False
Axes(xlValue).HasTitle = False
End With
With ActiveChart
HasAxis(xlCategory) = False
HasAxis(xlSeries) = False
HasAxis(xlValue) = True
End With
ActiveChart.Axes(xlCategory).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
HasMajorGridlines = False
HasMinorGridlines = False
End With
With ActiveChart.Axes(xlSeries)
HasMajorGridlines = False
HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
HasMajorGridlines = True
HasMinorGridlines = False
End With
ActiveChart.WallsAndGridlines2D = False
ActiveChart.ApplyDataLabels
Type:=xlDataLabelsShowNone, LegendKey:=False

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Keawee -

I think Tushar meant to put EnableEvents=True before End Sub.

For part 2, look at the Workbook_BeforeClose event.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Tushar Mehta wrote:

When you create the chart with Charts.Add, XL creates a chartsheet.
With the Location statement, the chart is moved from its own sheet to
the specified worksheet. This activates the Worksheet, which triggers
the code in Worksheet_Activate all over again.

One way around it is to create the chart with code such as:

Sub myChart()
If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub
ActiveSheet.ChartObjects.Add _
ActiveCell.Left, ActiveCell.Top, 300, 200
With ActiveSheet.ChartObjects(ActiveSheet.ChartObjects. Count).Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=ActiveSheet.Range("A1:A3")
End With
'...Rest of code...
End Sub

Another way around it is to use something like:

Private Sub Worksheet_Activate()
Application.EnableEvents = False
On Error GoTo errHandler
myChart
errHandler:
Application.EnableEvents = False
End Sub

He, he! The single most common programming mistake I make. Copying
and leaving alone the EnableEvents=False statement.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Keawee -

I think Tushar meant to put EnableEvents=True before End Sub.

For part 2, look at the Workbook_BeforeClose event.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Tushar Mehta wrote:

When you create the chart with Charts.Add, XL creates a chartsheet.
With the Location statement, the chart is moved from its own sheet to
the specified worksheet. This activates the Worksheet, which triggers
the code in Worksheet_Activate all over again.

One way around it is to create the chart with code such as:

Sub myChart()
If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub
ActiveSheet.ChartObjects.Add _
ActiveCell.Left, ActiveCell.Top, 300, 200
With ActiveSheet.ChartObjects(ActiveSheet.ChartObjects. Count).Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=ActiveSheet.Range("A1:A3")
End With
'...Rest of code...
End Sub

Another way around it is to use something like:

Private Sub Worksheet_Activate()
Application.EnableEvents = False
On Error GoTo errHandler
myChart
errHandler:
Application.EnableEvents = False
End Sub

That and ScreenUpdating. Then I wonder why the program misbehaves!

Tushar Mehta wrote:
He, he! The single most common programming mistake I make. Copying
and leaving alone the EnableEvents=False statement.