My code chart is looping

  • Thread starter Thread starter keawee
  • Start date Start date
K

keawee

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 Where:=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
 
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
 
Keawee -

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

For part 2, look at the Workbook_BeforeClose event.

- Jon
 
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
 
Back
Top