Worksheet_Change help

[Ayo]

Jim Thomlinson was helping me with this earlier but I ran into a problem. The
full code follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
Application.EnableEvents = False
With ActiveSheet

For Each c In Worksheets("Goals").Range("C3:C54").Cells
If c.Value = .Range("B4") And c.Offset(0, -1).Value =
Target.Value Then
.Range("I36") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c

End With
Application.EnableEvents = True
End If
End Sub

This one work once, when I open the file and change the value in B3; any
subseqeunt changes produces no results. Even when I try to put a break in the
code so I can troubleshoot, that don't work either. So I can't figure out
what the problem is or where it is. I really need this to work cause I still
have a long way to go with this project.
Thanks for all the help.
Ayo

 

[joel]

You must be reaching some sort of error and exiting the macro before events
are enabled. I would change you error trapping settings and see if you get
an error. From VBA menu

Tools - Options - General - Error Trapping

change to stop on all errors.

 

[Dave Peterson]

I'm guessing that you interrupted the code while you were testing and the
..enableevents flag was set to false.

That means that excel isn't looking for any other changes.

If you let the code finish nicely, one of the last things it does is toggle that
setting back to true.

If you're in developer's mode, you can do the same thing:
Open the VBE
Hit ctrl-g to see the immediate window
type:
application.enableevents = true
and hit enter.

Then back to excel to test it.

There are other reasons that this setting could be turned off, too. You could
have a different macro that disables events and then fails to re-enable them.

Your code could screw up and you could be chosing the End option to quit right
when events are disabled.

ps. I'd wouldn't use ActiveSheet in the code. I'd use Me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Address() = "$B$3" Then
Application.EnableEvents = False
With Me
For Each c In Worksheets("Goals").Range("C3:C54").Cells
If c.Value = .Range("B4") _
And c.Offset(0, -1).Value = Target.Value Then
.Range("I36") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With

Application.EnableEvents = True
End If
End Sub


Me refers to the object that owns the code and in this case, it's the worksheet
being changed. Your code could change sheets and the activesheet may not be the
one owning the code.

 

[Ayo]

Thanks Dave. I have another set of codes in the sub that was giving me some
problems so when I stop the macro, it doesn't execute the
"Application.EnableEvents = True" and theApplication.EnableEvents = False is
still active. That was what was causing the problem.
My problem is in this with statement:
With Me.Charts("Milestone Chart") I keep getting Subscript out of range on
this line then I have to stop the code. Can figure out what the problem is.
Do you have any ideas.
Thanks


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address() = "$B$3" Then
Application.EnableEvents = False
With Me

For Each c In Worksheets("Goals").Range("C3:C54").Cells
If c.Value = .Range("B4") And c.Offset(0, -1).Value =
Target.Value Then
.Range("I36") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c

With Me.Charts("Milestone Chart")
.HasTitle = True
.ChartTitle.Text = Range("B3") & " " & Range("B4") & " - NLP2"
'"First Quarter Sales"
End With

End With
Application.EnableEvents = True
End If
end Sub

 

[Dave Peterson]

try:

With me.chartobjects("milestone chart")

ps. I don't like spaces in names, but that shouldn't hurt you.

You can check the name of the chart by using something like this:


dim ChtObj as chartobject
for each chtobj in activesheet.chartobjects
msgbox "***" & chtobj.name & "***"
next chtobj


And if you want to rename the chart manually, ctrl click on its edge and type
the new name in the Namebox (to the left of the formula bar). Remember to hit
enter when you're done.

 

[Ayo]

Thanks Dave. I got it finally:
With Me.ChartObjects("Milestone Chart").Chart
.HasTitle = True
.ChartTitle.Text = Range("B3") & " " & Range("B4") & " - NLP2"
End With

 

[Dave Peterson]

Glad you got it working.

Thanks Dave. I got it finally:
With Me.ChartObjects("Milestone Chart").Chart
.HasTitle = True
.ChartTitle.Text = Range("B3") & " " & Range("B4") & " - NLP2"
End With