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! |
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 |