frubeng
05-22-2009, 04:10 AM
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!!
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!!