|
Out of stack space problem
Using Excel/97, I implemented the routine to display a calendar when a
particular cell was selected. All works fine because the range was fixed, however now I have to insert additional lines and extend the range to cope with these extra lines. The problem is that I don't know how many lines have been inserted. My original code simply checked to see if the selected cell was in M7:M26 and, if so, display the calendar Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("M7:M26"), Target) Is Nothing Then frmCalendar.Show End If End Sub To cope with the possibility of additional lines I "enhanced" the above code with the following, and inserted an "end of input marker" (Find_Value) after the input lines, however now I end up with the stack space problem. I've looked on the Call Stack and is it full to the brim with the Worksheet_SelectionChange entry. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub whereami_row = ActiveCell.Row whereami_col = ActiveCell.Column If whereami_col < 13 Then Exit Sub Found = 0 Start_Scan = 8 Find_Value = "STOP Processing Data Entry HW" Range("B8").Select Do Until Found = 1 If ActiveSheet.Cells(Start_Scan, 2).Value < Find_Value Then Value = ActiveSheet.Cells(Start_Scan, 2).Value Start_Scan = Start_Scan + 1 Else Found = 1 Last_Row = Start_Scan - 1 Range("M" & whereami_row).Select End If Loop If Not Application.Intersect(Range("M7:M" & Last_Row), Target) Is Nothing Then frmCalendar.Show End If End Sub Can anyone point me in the right direction to stop this recursive calling please ? Thanks in advance Fred |
Out of stack space problem
Your problem is you are selecting in the selection_change event causing the
event to trigger again. See the revised code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lastrow as Long If Target.Cells.Count 1 Then Exit Sub If Target.column < 13 Then Exit Sub LastRow = cells(rows.count,"M").End(xlup).Row If Not Application.Intersect(Range( _ "M7:M" & Last_Row), Target) Is Nothing Then frmCalendar.Show End If End Sub -- regards, Tom Ogilvy "Fred" wrote: Using Excel/97, I implemented the routine to display a calendar when a particular cell was selected. All works fine because the range was fixed, however now I have to insert additional lines and extend the range to cope with these extra lines. The problem is that I don't know how many lines have been inserted. My original code simply checked to see if the selected cell was in M7:M26 and, if so, display the calendar Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("M7:M26"), Target) Is Nothing Then frmCalendar.Show End If End Sub To cope with the possibility of additional lines I "enhanced" the above code with the following, and inserted an "end of input marker" (Find_Value) after the input lines, however now I end up with the stack space problem. I've looked on the Call Stack and is it full to the brim with the Worksheet_SelectionChange entry. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub whereami_row = ActiveCell.Row whereami_col = ActiveCell.Column If whereami_col < 13 Then Exit Sub Found = 0 Start_Scan = 8 Find_Value = "STOP Processing Data Entry HW" Range("B8").Select Do Until Found = 1 If ActiveSheet.Cells(Start_Scan, 2).Value < Find_Value Then Value = ActiveSheet.Cells(Start_Scan, 2).Value Start_Scan = Start_Scan + 1 Else Found = 1 Last_Row = Start_Scan - 1 Range("M" & whereami_row).Select End If Loop If Not Application.Intersect(Range("M7:M" & Last_Row), Target) Is Nothing Then frmCalendar.Show End If End Sub Can anyone point me in the right direction to stop this recursive calling please ? Thanks in advance Fred |
Out of stack space problem
Had a disagreement between my variable and yours. this is corrected
Your problem is you are selecting in the selection_change event causing the event to trigger again. See the revised code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Last_Row as Long If Target.Cells.Count 1 Then Exit Sub If Target.column < 13 Then Exit Sub Last_Row = cells(rows.count,"M").End(xlup).Row If Not Application.Intersect(Range( _ "M7:M" & Last_Row), Target) Is Nothing Then frmCalendar.Show End If End Sub -- regards, Tom Ogilvy "Tom Ogilvy" wrote: Your problem is you are selecting in the selection_change event causing the event to trigger again. See the revised code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lastrow as Long If Target.Cells.Count 1 Then Exit Sub If Target.column < 13 Then Exit Sub LastRow = cells(rows.count,"M").End(xlup).Row If Not Application.Intersect(Range( _ "M7:M" & Last_Row), Target) Is Nothing Then frmCalendar.Show End If End Sub -- regards, Tom Ogilvy "Fred" wrote: Using Excel/97, I implemented the routine to display a calendar when a particular cell was selected. All works fine because the range was fixed, however now I have to insert additional lines and extend the range to cope with these extra lines. The problem is that I don't know how many lines have been inserted. My original code simply checked to see if the selected cell was in M7:M26 and, if so, display the calendar Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("M7:M26"), Target) Is Nothing Then frmCalendar.Show End If End Sub To cope with the possibility of additional lines I "enhanced" the above code with the following, and inserted an "end of input marker" (Find_Value) after the input lines, however now I end up with the stack space problem. I've looked on the Call Stack and is it full to the brim with the Worksheet_SelectionChange entry. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub whereami_row = ActiveCell.Row whereami_col = ActiveCell.Column If whereami_col < 13 Then Exit Sub Found = 0 Start_Scan = 8 Find_Value = "STOP Processing Data Entry HW" Range("B8").Select Do Until Found = 1 If ActiveSheet.Cells(Start_Scan, 2).Value < Find_Value Then Value = ActiveSheet.Cells(Start_Scan, 2).Value Start_Scan = Start_Scan + 1 Else Found = 1 Last_Row = Start_Scan - 1 Range("M" & whereami_row).Select End If Loop If Not Application.Intersect(Range("M7:M" & Last_Row), Target) Is Nothing Then frmCalendar.Show End If End Sub Can anyone point me in the right direction to stop this recursive calling please ? Thanks in advance Fred |
| All times are GMT +1. The time now is 02:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com