Stack Space Problem with On Worksheet Change Event
 

Using Excel 2007, I want to use the worksheet change event to trap and
respond to changes in two columns. I can make it work if I trap for one
column change but not two. Then I run out of stack space. I put in a time
constant so I could verify the runaway recursion. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RName As Name ' New range
Dim ZName As Name 'CLASSID range
Dim YExp As Variant
Dim ZExp As Variant

Debug.Print Time

Set RName = ActiveWorkbook.Names("NewRange")
Set ZName = ActiveWorkbook.Names("ClassID")
Set YExp = Intersect(Target, Range(RName.RefersTo))
Set ZExp = Intersect(Target, Range(ZName.RefersTo))

On Error GoTo errhandler:

' If user types "new" into column O, replace with "NEW!"
If YExp Is Nothing Then
Else
If UCase(Target.Value) = "NEW" Then Target = "NEW!"
End If

' If user types any text into column, make the text upper case
' THIS APPEARS TO BE WHERE THE PROBLEM LIES!
If ZExp Is Nothing Then
Else
Target.Value = UCase(Target.Value)
End If

byebye:
Set YExp = Nothing
Set ZExp = Nothing
Set RName = Nothing
Set ZName = Nothing
Exit Sub

errhandler:
GoTo byebye
End Sub

Here is what happens: When I type "new" into column O, the debug prints the
time only twice, as it should. However, when I enter something into column N
("classID" col), I watch debug print the time over and over until the memory
stack fills up.

So, there has to be an efficient way of doing this. The worksheet
"auto-fill" technique is not reliable in this situation, and Data Validation
is too limited. Nor can I create a worksheet format to autoformat text as
upper case (I can't use an extra column in this case). Of course, I may
committing a stupid programming blunder, too. Any constructive criticism is
welcome!

Stack Space Problem with On Worksheet Change Event
 

Disable events before changing a value on the sheet. That will avoid
recursion.

Application.EnableEvents = False
Target.Cells(1,1).Value = UCASE(Target.Cells(1,1).Value) ' However you are
doing it
Application.EnableEvents = True



--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"George Atkins" wrote in message
...
Using Excel 2007, I want to use the worksheet change event to trap and
respond to changes in two columns. I can make it work if I trap for one
column change but not two. Then I run out of stack space. I put in a time
constant so I could verify the runaway recursion. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RName As Name ' New range
Dim ZName As Name 'CLASSID range
Dim YExp As Variant
Dim ZExp As Variant

Debug.Print Time

Set RName = ActiveWorkbook.Names("NewRange")
Set ZName = ActiveWorkbook.Names("ClassID")
Set YExp = Intersect(Target, Range(RName.RefersTo))
Set ZExp = Intersect(Target, Range(ZName.RefersTo))

On Error GoTo errhandler:

' If user types "new" into column O, replace with "NEW!"
If YExp Is Nothing Then
Else
If UCase(Target.Value) = "NEW" Then Target = "NEW!"
End If

' If user types any text into column, make the text upper case
' THIS APPEARS TO BE WHERE THE PROBLEM LIES!
If ZExp Is Nothing Then
Else
Target.Value = UCase(Target.Value)
End If

byebye:
Set YExp = Nothing
Set ZExp = Nothing
Set RName = Nothing
Set ZName = Nothing
Exit Sub

errhandler:
GoTo byebye
End Sub

Here is what happens: When I type "new" into column O, the debug prints
the
time only twice, as it should. However, when I enter something into column
N
("classID" col), I watch debug print the time over and over until the
memory
stack fills up.

So, there has to be an efficient way of doing this. The worksheet
"auto-fill" technique is not reliable in this situation, and Data
Validation
is too limited. Nor can I create a worksheet format to autoformat text as
upper case (I can't use an extra column in this case). Of course, I may
committing a stupid programming blunder, too. Any constructive criticism
is
welcome!

Stack Space Problem with On Worksheet Change Event
 

Before making any change to a cell, your code should off event
handling. E.g.,

Application.EnableEvents = False
' your code
Application.EnableEvents = True


If you do not turn off events, the code will change a cell, and that
change triggers the Change event, which changes a cell, which triggers
Change, which changes a cell, which triggers Change, and so on and on.
Eventually, VBA gives up.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 29 May 2009 11:39:01 -0700, George Atkins
wrote:

Using Excel 2007, I want to use the worksheet change event to trap and
respond to changes in two columns. I can make it work if I trap for one
column change but not two. Then I run out of stack space. I put in a time
constant so I could verify the runaway recursion. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RName As Name ' New range
Dim ZName As Name 'CLASSID range
Dim YExp As Variant
Dim ZExp As Variant

Debug.Print Time

Set RName = ActiveWorkbook.Names("NewRange")
Set ZName = ActiveWorkbook.Names("ClassID")
Set YExp = Intersect(Target, Range(RName.RefersTo))
Set ZExp = Intersect(Target, Range(ZName.RefersTo))

On Error GoTo errhandler:

' If user types "new" into column O, replace with "NEW!"
If YExp Is Nothing Then
Else
If UCase(Target.Value) = "NEW" Then Target = "NEW!"
End If

' If user types any text into column, make the text upper case
' THIS APPEARS TO BE WHERE THE PROBLEM LIES!
If ZExp Is Nothing Then
Else
Target.Value = UCase(Target.Value)
End If

byebye:
Set YExp = Nothing
Set ZExp = Nothing
Set RName = Nothing
Set ZName = Nothing
Exit Sub

errhandler:
GoTo byebye
End Sub

Here is what happens: When I type "new" into column O, the debug prints the
time only twice, as it should. However, when I enter something into column N
("classID" col), I watch debug print the time over and over until the memory
stack fills up.

So, there has to be an efficient way of doing this. The worksheet
"auto-fill" technique is not reliable in this situation, and Data Validation
is too limited. Nor can I create a worksheet format to autoformat text as
upper case (I can't use an extra column in this case). Of course, I may
committing a stupid programming blunder, too. Any constructive criticism is
welcome!

Stack Space Problem with On Worksheet Change Event
 

Ah ha! I should have figured there was a way to do that, but I missed it.
Thanks for pointing me in the correct direction. Love your web site, by the
way.

"Chip Pearson" wrote:

Before making any change to a cell, your code should off event
handling. E.g.,

Application.EnableEvents = False
' your code
Application.EnableEvents = True


If you do not turn off events, the code will change a cell, and that
change triggers the Change event, which changes a cell, which triggers
Change, which changes a cell, which triggers Change, and so on and on.
Eventually, VBA gives up.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 29 May 2009 11:39:01 -0700, George Atkins
wrote:

Using Excel 2007, I want to use the worksheet change event to trap and
respond to changes in two columns. I can make it work if I trap for one
column change but not two. Then I run out of stack space. I put in a time
constant so I could verify the runaway recursion. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RName As Name ' New range
Dim ZName As Name 'CLASSID range
Dim YExp As Variant
Dim ZExp As Variant

Debug.Print Time

Set RName = ActiveWorkbook.Names("NewRange")
Set ZName = ActiveWorkbook.Names("ClassID")
Set YExp = Intersect(Target, Range(RName.RefersTo))
Set ZExp = Intersect(Target, Range(ZName.RefersTo))

On Error GoTo errhandler:

' If user types "new" into column O, replace with "NEW!"
If YExp Is Nothing Then
Else
If UCase(Target.Value) = "NEW" Then Target = "NEW!"
End If

' If user types any text into column, make the text upper case
' THIS APPEARS TO BE WHERE THE PROBLEM LIES!
If ZExp Is Nothing Then
Else
Target.Value = UCase(Target.Value)
End If

byebye:
Set YExp = Nothing
Set ZExp = Nothing
Set RName = Nothing
Set ZName = Nothing
Exit Sub

errhandler:
GoTo byebye
End Sub

Here is what happens: When I type "new" into column O, the debug prints the
time only twice, as it should. However, when I enter something into column N
("classID" col), I watch debug print the time over and over until the memory
stack fills up.

So, there has to be an efficient way of doing this. The worksheet
"auto-fill" technique is not reliable in this situation, and Data Validation
is too limited. Nor can I create a worksheet format to autoformat text as
upper case (I can't use an extra column in this case). Of course, I may
committing a stupid programming blunder, too. Any constructive criticism is
welcome!

Stack Space Problem with On Worksheet Change Event
 

Spot on, Tim. Thanks a lot. I just completely overlooked that simple solution
(simple once somebody tells you, that is)! I appreciate your help.

"Tim Zych" wrote:

Disable events before changing a value on the sheet. That will avoid
recursion.

Application.EnableEvents = False
Target.Cells(1,1).Value = UCASE(Target.Cells(1,1).Value) ' However you are
doing it
Application.EnableEvents = True



--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"George Atkins" wrote in message
...
Using Excel 2007, I want to use the worksheet change event to trap and
respond to changes in two columns. I can make it work if I trap for one
column change but not two. Then I run out of stack space. I put in a time
constant so I could verify the runaway recursion. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RName As Name ' New range
Dim ZName As Name 'CLASSID range
Dim YExp As Variant
Dim ZExp As Variant

Debug.Print Time

Set RName = ActiveWorkbook.Names("NewRange")
Set ZName = ActiveWorkbook.Names("ClassID")
Set YExp = Intersect(Target, Range(RName.RefersTo))
Set ZExp = Intersect(Target, Range(ZName.RefersTo))

On Error GoTo errhandler:

' If user types "new" into column O, replace with "NEW!"
If YExp Is Nothing Then
Else
If UCase(Target.Value) = "NEW" Then Target = "NEW!"
End If

' If user types any text into column, make the text upper case
' THIS APPEARS TO BE WHERE THE PROBLEM LIES!
If ZExp Is Nothing Then
Else
Target.Value = UCase(Target.Value)
End If

byebye:
Set YExp = Nothing
Set ZExp = Nothing
Set RName = Nothing
Set ZName = Nothing
Exit Sub

errhandler:
GoTo byebye
End Sub

Here is what happens: When I type "new" into column O, the debug prints
the
time only twice, as it should. However, when I enter something into column
N
("classID" col), I watch debug print the time over and over until the
memory
stack fills up.

So, there has to be an efficient way of doing this. The worksheet
"auto-fill" technique is not reliable in this situation, and Data
Validation
is too limited. Nor can I create a worksheet format to autoformat text as
upper case (I can't use an extra column in this case). Of course, I may
committing a stupid programming blunder, too. Any constructive criticism
is
welcome!