Out of stack space problem

[Fred]

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

 

[Guest]

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

 

[Guest]

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