Calculating results when data changes
 

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

Calculating results when data changes
 

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.


"Gary Paris" wrote in message
...
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

Calculating results when data changes
 

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 wrote:

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

--

Dave Peterson

Calculating results when data changes
 

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?




"Gary Paris" wrote in message
...
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

Calculating results when data changes
 

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 wrote:

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?

"Gary Paris" wrote in message
...
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



--

Dave Peterson

Calculating results when data changes
 

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Calculating results when data changes
 

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 wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

Calculating results when data changes
 

On Fri, 24 Dec 2004 17:08:00 -0800, "Gary Paris" wrote:

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.

Calculating results when data changes
 

On Fri, 24 Dec 2004 15:48:46 -0800, "Tim Williams"
<saxifrax@pacbell*dot*net wrote:

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.

Calculating results when data changes
 

On Fri, 24 Dec 2004 17:08:00 -0800, Gary Paris wrote:

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",Ra nge("Paid_By").Offset(0,-2))
Dom_Total = WorksheetFunction.SumIf(Range("Paid_By"),"Dom",Ran ge("Paid_By").Offset(0,-2))

Don <donwiss at panix.com.

Incredible code ! Thanks
 

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

"Don Wiss" wrote in message
...
On Fri, 24 Dec 2004 17:08:00 -0800, Gary Paris wrote:

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",Ra nge("Paid_By").Offset(0,-2))
Dom_Total =
WorksheetFunction.SumIf(Range("Paid_By"),"Dom",Ran ge("Paid_By").Offset(0,-2))

Don <donwiss at panix.com.