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
 

Doh, stupid strikes again !

It was because of the Range (..).Select statements, moving to a
different cell and causing a re-invocation of the code. Remove them
and all works fine.

Apologies for the "multiple posts", another network glitch.

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


"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