Why does this Worksheet_Change Sub NOT trigger the macro that it calls
although it triggers the selection of range b16 at the end and
although the called macro works perfectly if called in another way
(e.g.by F5).
There are three charts in my workbook that I want to automaticly
update when the "Sensi" range is touched. Is there any better way to
do what I'm trying? I can't trust the automatic axis scaling of my
charts.
Thank you very much for your help.
Herman


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sensi As Range
'Application.EnableEvents = False
Application.ScreenUpdating = False
Set Sensi = Range("B2:C14")
If Intersect(Target, Sensi) Is Nothing Then
Exit Sub
End If
Call BVCashChart
Range("b16").Select
Application.ScreenUpdating = True
End Sub


Sub BVCashChart()
'herman 06/02/04
Dim jaar As Range, BVW As Range, cash As Range, Kap As Range, Rcour As
Range, DivPer As Range
Dim Graf18 As Range, Graf3 As Range, Graf24 As Range

Set jaar = Sheets("par").Range("d27", Range("d27").Offset(0,
Range("b14").Value))
Set cash = Sheets("par").Range("d28", Range("d28").Offset(0,
Range("b14").Value))
Set BVW = Sheets("par").Range("d29", Range("d29").Offset(0,
Range("b14").Value))
Set Kap = Sheets("par").Range("d30", Range("d30").Offset(0,
Range("b14").Value))
Set Rcour = Sheets("par").Range("d31", Range("d31").Offset(0,
Range("b14").Value))
Set DivPer = Sheets("par").Range("q1:r11")

Set Graf24 = Union(jaar, cash, BVW)
Set Graf3 = Union(jaar, Kap, Rcour)


ActiveSheet.ChartObjects("Chart 24").Activate
On Error Resume Next
With ActiveChart
.SetSourceData Source:=Graf24
With .SeriesCollection(1)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 11
.DataLabels.Font.Size = 9
End With
With .SeriesCollection(2)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 7
.DataLabels.Font.Size = 9
End With
End With
ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart
.SetSourceData Source:=Graf3
With .SeriesCollection(1)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 11
.DataLabels.Font.Size = 9
End With
With .SeriesCollection(2)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 7
.DataLabels.Font.Size = 9
End With
End With
ActiveSheet.ChartObjects("Chart 18").Activate

With ActiveChart
.SetSourceData Source:=DivPer
With .Axes(xlValue)
.MinimumScale = Range("t1").Value
.MaximumScale = Range("t2").Value
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
.DisplayUnit = xlThousands
.HasDisplayUnitLabel = False
End With
End With
On Error GoTo 0
Range("B14").Select
End Sub

Herman,

Is the worksheet_change code in the worksheet module?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"herman" wrote in message
om...
Why does this Worksheet_Change Sub NOT trigger the macro that it calls
although it triggers the selection of range b16 at the end and
although the called macro works perfectly if called in another way
(e.g.by F5).
There are three charts in my workbook that I want to automaticly
update when the "Sensi" range is touched. Is there any better way to
do what I'm trying? I can't trust the automatic axis scaling of my
charts.
Thank you very much for your help.
Herman


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sensi As Range
'Application.EnableEvents = False
Application.ScreenUpdating = False
Set Sensi = Range("B2:C14")
If Intersect(Target, Sensi) Is Nothing Then
Exit Sub
End If
Call BVCashChart
Range("b16").Select
Application.ScreenUpdating = True
End Sub


Sub BVCashChart()
'herman 06/02/04
Dim jaar As Range, BVW As Range, cash As Range, Kap As Range, Rcour As
Range, DivPer As Range
Dim Graf18 As Range, Graf3 As Range, Graf24 As Range

Set jaar = Sheets("par").Range("d27", Range("d27").Offset(0,
Range("b14").Value))
Set cash = Sheets("par").Range("d28", Range("d28").Offset(0,
Range("b14").Value))
Set BVW = Sheets("par").Range("d29", Range("d29").Offset(0,
Range("b14").Value))
Set Kap = Sheets("par").Range("d30", Range("d30").Offset(0,
Range("b14").Value))
Set Rcour = Sheets("par").Range("d31", Range("d31").Offset(0,
Range("b14").Value))
Set DivPer = Sheets("par").Range("q1:r11")

Set Graf24 = Union(jaar, cash, BVW)
Set Graf3 = Union(jaar, Kap, Rcour)


ActiveSheet.ChartObjects("Chart 24").Activate
On Error Resume Next
With ActiveChart
.SetSourceData Source:=Graf24
With .SeriesCollection(1)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 11
.DataLabels.Font.Size = 9
End With
With .SeriesCollection(2)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 7
.DataLabels.Font.Size = 9
End With
End With
ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart
.SetSourceData Source:=Graf3
With .SeriesCollection(1)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 11
.DataLabels.Font.Size = 9
End With
With .SeriesCollection(2)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 7
.DataLabels.Font.Size = 9
End With
End With
ActiveSheet.ChartObjects("Chart 18").Activate

With ActiveChart
.SetSourceData Source:=DivPer
With .Axes(xlValue)
.MinimumScale = Range("t1").Value
.MaximumScale = Range("t2").Value
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
.DisplayUnit = xlThousands
.HasDisplayUnitLabel = False
End With
End With
On Error GoTo 0
Range("B14").Select
End Sub

Hi Herman
two ideas:
- is the code in the worksheet module of your sheet?
- Maybe the Application events are disbaled (due to testing / error,
etc): Input the statement
Application.EnableEvents = True
in the Intermediate windows and try again

Frank


herman wrote:
Why does this Worksheet_Change Sub NOT trigger the macro that it
calls
although it triggers the selection of range b16 at the end and
although the called macro works perfectly if called in another way
(e.g.by F5).
There are three charts in my workbook that I want to automaticly
update when the "Sensi" range is touched. Is there any better way to
do what I'm trying? I can't trust the automatic axis scaling of my
charts.
Thank you very much for your help.
Herman

Get rid of the On Error Resume Next statement and you might discover
why it doesn't work.

Also, it is rarely necessary to select/activate objects before
accessing/updating their properties/methods. For example, instead of

ActiveSheet.ChartObjects("Chart 24").Activate
With ActiveChart
....

use

With ActiveSheet.ChartObjects("Chart 24").Chart
....

Also, keep in mind that selecting/activating objects and/or making
changes to the XL environment/workbook from inside an event procedure
can trigger other event procedures and might even recursively call the
currently active one itself.

--
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...
Why does this Worksheet_Change Sub NOT trigger the macro that it calls
although it triggers the selection of range b16 at the end and
although the called macro works perfectly if called in another way
(e.g.by F5).
There are three charts in my workbook that I want to automaticly
update when the "Sensi" range is touched. Is there any better way to
do what I'm trying? I can't trust the automatic axis scaling of my
charts.
Thank you very much for your help.
Herman


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sensi As Range
'Application.EnableEvents = False
Application.ScreenUpdating = False
Set Sensi = Range("B2:C14")
If Intersect(Target, Sensi) Is Nothing Then
Exit Sub
End If
Call BVCashChart
Range("b16").Select
Application.ScreenUpdating = True
End Sub


Sub BVCashChart()
'herman 06/02/04
Dim jaar As Range, BVW As Range, cash As Range, Kap As Range, Rcour As
Range, DivPer As Range
Dim Graf18 As Range, Graf3 As Range, Graf24 As Range

Set jaar = Sheets("par").Range("d27", Range("d27").Offset(0,
Range("b14").Value))
Set cash = Sheets("par").Range("d28", Range("d28").Offset(0,
Range("b14").Value))
Set BVW = Sheets("par").Range("d29", Range("d29").Offset(0,
Range("b14").Value))
Set Kap = Sheets("par").Range("d30", Range("d30").Offset(0,
Range("b14").Value))
Set Rcour = Sheets("par").Range("d31", Range("d31").Offset(0,
Range("b14").Value))
Set DivPer = Sheets("par").Range("q1:r11")

Set Graf24 = Union(jaar, cash, BVW)
Set Graf3 = Union(jaar, Kap, Rcour)


ActiveSheet.ChartObjects("Chart 24").Activate
On Error Resume Next
With ActiveChart
.SetSourceData Source:=Graf24
With .SeriesCollection(1)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 11
.DataLabels.Font.Size = 9
End With
With .SeriesCollection(2)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 7
.DataLabels.Font.Size = 9
End With
End With
ActiveSheet.ChartObjects("Chart 3").Activate
With ActiveChart
.SetSourceData Source:=Graf3
With .SeriesCollection(1)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 11
.DataLabels.Font.Size = 9
End With
With .SeriesCollection(2)
.DataLabels.Delete
.Points(Range("b14").Value + 1).ApplyDataLabels
AutoText:=True, ShowValue:=True
.DataLabels.Font.ColorIndex = 7
.DataLabels.Font.Size = 9
End With
End With
ActiveSheet.ChartObjects("Chart 18").Activate

With ActiveChart
.SetSourceData Source:=DivPer
With .Axes(xlValue)
.MinimumScale = Range("t1").Value
.MaximumScale = Range("t2").Value
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
.DisplayUnit = xlThousands
.HasDisplayUnitLabel = False
End With
End With
On Error GoTo 0
Range("B14").Select
End Sub