Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change help
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. "Ayo" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change help
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change help
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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change help
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change help
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change help
Glad you got it working.
Ayo wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Programming | |||
Worksheet_Change | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |