PDA

View Full Version : Worksheet_change



frubeng
05-19-2009, 11:25 AM
I have the following in Sheet1:
Private Sub worksheet_change(ByVal Target As Range)
Call main
End Sub


And in module 1

Sub main()
ActiveWorkbook.ActiveSheet.Cells(2, 7) = 0
sumtotal = 0
For i = 1 To Month(Now)
row2 = row2 + Row
Sum = 0
Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 2), Cells(row2 + 200, 2)), 0)
If Not IsEmpty(ActiveWorkbook.ActiveSheet.Cells(Row + row2 - 1, 1)) Then
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 1).EntireRow.Insert
Row = Row + 1
End If
'Summing all the comms for the monthly total
For j = 1 To Row - 3
Sum = Sum + ActiveWorkbook.ActiveSheet.Cells(row2 + j + 1, 4)
Next j
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 4) = Sum
sumtotal = sumtotal + Sum
Next i
ActiveWorkbook.ActiveSheet.Cells(2, 7) = sumtotal
End Sub


When I change a cell in the spreadsheet, it seems the program runs forever. When i stop it (by ctrl+break) it gives me a few error messages, and excel even crashes sometimes.

What is wrong?
(When i ink the main function to a button it works perfectly)

Thank you for your help!!

Bob Phillips
05-19-2009, 11:32 AM
Try this



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

frubeng
05-19-2009, 11:40 AM
I'm really impressed by the help one can get here! Thanks so much!!