Thread
:
Worksheet_Change sub does not trigger a called macro
View Single Post
#
4
Posted to microsoft.public.excel.programming
Tushar Mehta
external usenet poster
Posts: 1,071
Worksheet_Change sub does not trigger a called macro
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
Reply With Quote
Tushar Mehta
View Public Profile
Find all posts by Tushar Mehta