Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stack Space Problem with On Worksheet Change Event | Excel Programming | |||
Stack Space Problem with On Worksheet Change Event | Excel Programming | |||
Stack Space Problem with On Worksheet Change Event | Excel Programming | |||
Out of stack space problem | Excel Programming | |||
Out of stack space problem | Excel Programming |