update plots with cell info change

Jonas.S

Hi,

I have a plot macro, see code below which I would like to continuously update the plots when a change been made to reference cells(E45:G45 and N3:P3). Anyone out there who can help me with this

Sub Engine()

COG_t = Range("E45:G45")
Eng_s = Range("N3:P3")

'Declaring variables
Dim xmax, xmin, ymax, ymin
Dim x, y

'Calculats the engine's position

xmax = COG_t(1, 1) + Eng_s(1, 1)
xmin = COG_t(1, 1) - Eng_s(1, 1)

ymax = COG_t(1, 2) + Eng_s(1, 2)
ymin = COG_t(1, 2) - Eng_s(1, 2)

'Creates the corner points of the engine

x = Array(xmax, xmin)
y = Array(ymax, ymin)

Engine_C = Array(x, y)

Module2.EngineContourPlot x, y

End Sub

In module2...

Sub EngineContourPlot(x, y)

Charts.Add.Name = "XY_View"
With ActiveChart

.ChartType = xlXYScatterSmoothNoMarkers
.SeriesCollection.NewSeries

With .SeriesCollection(.SeriesCollection.Count)
.Values = x
.XValues = y
End With
End With

End Sub

Gratful for help

\Jonas

Andy Pope


Hi,

You could put so code in the Change event of the worksheet that has the data.
Right click the sheet tab and pick View Code.

Add the following to the sheets code module.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E45:G45")) Is Nothing Or _
Not Intersect(Target, Range("N3:P3")) Is Nothing Then
MsgBox "Engine"
End If

End Sub

One problem will be fact you add a chart every time the EnginePlotContour is run.
Perhaps you should check for a chart sheet before adding a new one.

Jonas.S

Thanks a lot Andy!