How Sheet formulae is called

[frubeng]

Hi, I have a basic question about how the VB code is called in general.

I have in my Sheet1 tab:

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

and in my Module1 tab:

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 originally wrote the code, it worked great, calling main as soon as a change was made byt the user. Then i did some more stuff on the workbook and it stopped calling main automatically (it would only work by going into the VB editor and pressing F5). What's happening here?

Also, on another workbook, I just have the regular

Private Sub Worksheet_calculate()
    Call main
End Sub

And i have the same issue sometimes, that it would sometimes call it automatically and sometimes would have to be triggered.
Also, if it does run automatically, does it run every few seconds? (if so can the time interval be set??), or does it just runs as soon as memory is available?

I guess my questions are pretty basic, but i would appreciate some light!

Thank you!!

[Bob Phillips]

Sounds to me that you have hit an error when events are turned off and so they didn't get turned on again. You need to reset events and add some error handling to your code.

[mdmackillop]

It is possible that your code has errored, and you have exited the code without resetting events
Run this macro to reset events

 
Sub Enables() 
    Application.EnableEvents = True
End Sub

[frubeng]

Great, that defintiely solves the problem!

I was also wondering if you had insight on this part:

Also, on another workbook, I just have the regular

Private Sub Worksheet_calculate()
    Call main 
End Sub

And i have the same issue sometimes, that it would sometimes call it automatically and sometimes would have to be triggered.
Also, if it does run automatically, does it run every few seconds? (if so can the time interval be set??), or does it just runs as soon as memory is available?


Thank you so much for the help

[mdmackillop]

Suggestions:
Always use Option Explicit and Dim your variables.
Use a With statement instead of repeated qualifying with ActiveWorkbook.ActiveSheet
Ensure all references are qualified.

[Bob Phillips]

Error handling as I said before

Private Sub worksheet_change(ByVal Target As Range) 
    On Error Goto ws_exit:
    Application.EnableEvents = False 
    Call main 
    ws_exit:
    Application.EnableEvents = True 
End Sub

[frubeng]

So it seems that even with error handling, the program only works some of the time...

I have in Sheet1:

Sub Enables()
    Application.EnableEvents = True
End Sub

Private Sub worksheet_change(ByVal Target As Range)
    On Error GoTo ws_exit:
    Application.EnableEvents = False
    Call main
    ws_exit:
    Application.EnableEvents = True
End Sub

and in module1:

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

the macro is not triggered when a change is made on the sheet. What is going wrong?

Thanks.

[Bob Phillips]

Is the event macro in the worksheet code module? You said sometimes, does it work not at all, or some of the time?

[frubeng]

Is the event macro in the worksheet code module? You said sometimes, does it work not at all, or some of the time?

The event macro is in the sheet, the Main macro is in the module.
It worked fine when I wrote the sheet, but since i have saved and closed and then re-opened, it doesn't get launched. (if i go into the code and press play, then it runs, but ti doesnt get triggered by the event code)

[Bob Phillips]

I am struggling to see why, can you post the workbook?