worksheet change problems
 

i have worksheet change code that calls another sub when a cell in a range is
changed (actually 2, but i'm only concerned with the 2nd one). but i don't want
it to fire if i blank the cell out, because there is no value to use. so i check
to see if it's blank and then exit the sub. but after i do this, neither
condition runs unless i exit excel and restart it. just closing the workbook
doesn't help.

anyone know why this happens?



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
Run_all
End If

'this one is the one i'm referring to
If Not Intersect(Target, Range(PL_Range)) Is Nothing Then
If Target.Value = "" Then Exit Sub
pRow = Target.Row
Batching
End If
On Error GoTo 0
ws_exit:
Application.EnableEvents = True
End Sub



--


Gary

worksheet change problems
 

think i found it, that application events statement.

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i have worksheet change code that calls another sub when a cell in a range is
changed (actually 2, but i'm only concerned with the 2nd one). but i don't
want
it to fire if i blank the cell out, because there is no value to use. so i
check
to see if it's blank and then exit the sub. but after i do this, neither
condition runs unless i exit excel and restart it. just closing the workbook
doesn't help.

anyone know why this happens?



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
Run_all
End If

'this one is the one i'm referring to
If Not Intersect(Target, Range(PL_Range)) Is Nothing Then
If Target.Value = "" Then Exit Sub
pRow = Target.Row
Batching
End If
On Error GoTo 0
ws_exit:
Application.EnableEvents = True
End Sub



--


Gary

worksheet change problems
 

The Application.EnableEvents was never set back to true. Most likely you hit
an error which bypassed the enable. I would comment out all your on error
statements and re-run the macro. When you get the error the code will stop
and allow you to fix the problem.


"Gary Keramidas" wrote:

i have worksheet change code that calls another sub when a cell in a range is
changed (actually 2, but i'm only concerned with the 2nd one). but i don't want
it to fire if i blank the cell out, because there is no value to use. so i check
to see if it's blank and then exit the sub. but after i do this, neither
condition runs unless i exit excel and restart it. just closing the workbook
doesn't help.

anyone know why this happens?



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
Run_all
End If

'this one is the one i'm referring to
If Not Intersect(Target, Range(PL_Range)) Is Nothing Then
If Target.Value = "" Then Exit Sub
pRow = Target.Row
Batching
End If
On Error GoTo 0
ws_exit:
Application.EnableEvents = True
End Sub



--


Gary

worksheet change problems
 

I believe you meant to write:

If Target.Value = "" Then GoTo ws_exit

which would have correctly reset the application.enableevents flag back to
true.

"Gary Keramidas" wrote:

i have worksheet change code that calls another sub when a cell in a range is
changed (actually 2, but i'm only concerned with the 2nd one). but i don't want
it to fire if i blank the cell out, because there is no value to use. so i check
to see if it's blank and then exit the sub. but after i do this, neither
condition runs unless i exit excel and restart it. just closing the workbook
doesn't help.

anyone know why this happens?



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
Run_all
End If

'this one is the one i'm referring to
If Not Intersect(Target, Range(PL_Range)) Is Nothing Then
If Target.Value = "" Then Exit Sub
pRow = Target.Row
Batching
End If
On Error GoTo 0
ws_exit:
Application.EnableEvents = True
End Sub



--


Gary