Calculating results when data changes

[Gary Paris]

I have a range of data that encompasses anywhere from 20 rows to 50 rows and
5 columns.

I have a routine that I call Calculate_Results that is called from the
Workbook_SheetChange routine.
The problem is that once the results are calculated, I would like to place
totals into a cell on another
worksheet. Problem is the routine goes forever. Calculate_Results
generates a SheetChange and SheetChange calls Calculate_Results.

How can I call Calculate_Results whenever there is data modification in the
area I would like to change?

Thanks,

Gary

 

[Tim Williams]

You need to either disable events while doing your Calculate_Results
stuff

application.enableevents=false
Calculate_Results
application.enableevents=true

or use a flag inside your sheetchange handler

Static bProcessing as boolean

if bprocessing then exit sub
bProcessing=true
Calculate_Results
bProcessing=false


Hope this helps
Tim.

 

[Dave Peterson]

You can tell excel to stop looking for changes.

Then your code can make the change it needs to make and the event won't be
called.

In general:

application.enableevents = false 'tell excel to stop looking
'your code that does something that would have fired an event
application.enableevents = true 'tell excel to start looking again.

 

[Gary Paris]

Thanks to the guys who replied, but I have another question. Is it possible
to call the Calculate_Results routine only when data changes in the named
Range?

 

[Dave Peterson]

You could do something like this that just gets out early if the change isn't in
the right range:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("NamedRangeHere")) Is Nothing Then
Exit Sub
End If
End Sub

(This means that each sheet has to have a range named: NamedRangeHere)

 

[Gary Paris]

I put this code in the proper place and it works. Another problem is that I
have three worksheets in the book and I have a Sheets("Expenses").Select
statement in the routine.

Each time the routine runs, I am directed to the Expenses sheet. Is there
anyway around this? Can I possibly save the current sheet name, the current
cell I am in and then go back when the routine completes?

Thanks,

Gary

 

[Dave Peterson]

Most times, you don't need to select a worksheet or range to work with it. You
can modify objects directly.

worksheets("expenses").select
range("a1").select
activecell.value = "Hi"

could be replaced with:
worksheets("expenses").range("a1").value = "Hi"

It usually makes the code much easier to read/decipher and it makes the code
execute faster.

===
But you could do:

Dim CurSelection as range
Dim curActivecell as range

set curSelection = Selection
set curActivecell = activecell

'do your stuff

application.goto curSelection
curactivecell.activate

 

[Gary Paris]

Thanks for your input. Code works OK, but another question. Is there a way
that I can call my Calc routine only when leaving the Expenses sheet? But
If that can be done, there is still that worksheets.select command that
changes to the Expenses sheet. I'll paste the code that I am using in my
routine:

' *************************************************************
Sub Calc_Expenses()

Dim myCell As Range
Dim curSelection As Range
Dim curActiveCell As Range

Dim Gary_Total As Currency
Dim Dom_Total As Currency

Set curSelection = Selection
Set curActiveCell = ActiveCell

Sheets("Expenses").Select

For Each myCell In Range("Paid_By").Cells
Range(myCell.Address).Select

Select Case myCell

Case "Gary"
Gary_Total = Gary_Total + ActiveCell.Offset(0, -2).Value

Case "Dom"
Dom_Total = Dom_Total + ActiveCell.Offset(0, -2).Value

End Select

Next myCell

Application.EnableEvents = False

Worksheets("Amounts").Range("cash_to_gary").Value = Gary_Total
Worksheets("Amounts").Range("cash_to_dom").Value = Dom_Total

Application.EnableEvents = True

Application.Goto curSelection
curActiveCell.Activate


End Sub
' *************************************************************

This code works but I don't like the actions in Excel. When going back to
the previous worksheet and cell, the spreadsheet activates the cell and
shows as the first line.

Thanks,

Gary

 

[Don Wiss]

Thanks for your input. Code works OK, but another question. Is there a way
that I can call my Calc routine only when leaving the Expenses sheet?

Put this in your ThisWorkBook module:

Sub Workbook_SheetDeactivate(ByVal Sh As Object)

If Sh.Name = "Expenses" Then
' code goes here
End If

End Sub

Don <donwiss at panix.com>.

 

[Don Wiss]

or use a flag inside your sheetchange handler

Static bProcessing as boolean

if bprocessing then exit sub
bProcessing=true
Calculate_Results
bProcessing=false

I started with the flag way, and I find it flexible to use for all sorts
of: don't do this if a macro is running. It is my Macro Running flag. I
define it globally as:

Public MRflag as Boolean

I've never used Static.

Don <donwiss at panix.com>.

 

[Don Wiss]

Sheets("Expenses").Select

For Each myCell In Range("Paid_By").Cells
Range(myCell.Address).Select

Select Case myCell

Case "Gary"
Gary_Total = Gary_Total + ActiveCell.Offset(0, -2).Value

Case "Dom"
Dom_Total = Dom_Total + ActiveCell.Offset(0, -2).Value

End Select

Next myCell

This is how I would do this. No sheet selection needed.

Gary_Total = WorksheetFunction.SumIf(Range("Paid_By"),"Gary",Range("Paid_By").Offset(0,-2))
Dom_Total = WorksheetFunction.SumIf(Range("Paid_By"),"Dom",Range("Paid_By").Offset(0,-2))

Don <donwiss at panix.com>.

 

[Gary Paris]

Wow,

You did in two lines what it took me many lines and quite a few hours to
figure out. Thanks for the great code snippit. Plus, the sheets don't get
selected and the active cell isn't changed. I still have
lots to learn, but I guess that is what the newsgroups are all about!

Gary