Out of Stack Space - run macro on worksheet update

[rossbritton26]

Hi,

i have the following macro which calculates the hours worked, money earnt and holiday pay accrued per month in a summary table, by using the month function on the date of each shift and adding up all the values from each month!!

Sub ross()

    Dim i As Integer
    Dim n As Integer
    
    Range("N3:P10").ClearContents

    i = 1
    For n = 5 To 12
        hours = 0
        earnt = 0
        holiday = 0
        
        Range("A2").Select

        Do Until (Selection.Offset(0, 0) = "")

            If Range("B" & i + 1).Value = n Then

                hours = hours + Range("E" & i + 1).Value
                Range("N" & n - 2).Value = hours
                earnt = earnt + Range("G" & i + 1).Value
                Range("O" & n - 2).Value = earnt
                holiday = holiday + Range("I" & i + 1).Value
                Range("P" & n - 2).Value = holiday
                i = i + 1

            Else
            End If
            Selection.Offset(1, 0).Select
        Loop

    Next n

End Sub

This works fine and the summary table is populated as required when the macro is run, but i would like the macro to run every time a new shift is entered.

i have tried running the macro using

Private Sub Worksheet_Change(ByVal Target As Range)

    Run "ross"

End Sub

which has worked on previous spreadsheets but i am getting Error Code - 28, Out of Stack Space, anyone got any ideas why this is???

Ross.

 

[nightcrawler23]

Hi,

Private Sub Worksheet_Change(ByVal Target As Range)

[COLOR="Red"]    ross[/COLOR]

End Sub

Ross.

CAn u try this.

 

[Norie]

The worksheet change event is called when there's a change on the worksheet.

Your sub makes changes on the worksheet.

When those changes happen the worksheet change event is triggered, the sub is called, the sub changes the worksheet, the change event is triggered, the sub is called and so on....

Basically you end up in an infinite loop.

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Call ross
    Application.EnableEvents = True
End Sub

PS You can look at the stack if you goto View>Call Stack...

 

[rossbritton26]

Thank you very much for the replies,

Norie i have used your code and it works perfectly, thank you very much for the help.

Ross