calculationstate

Are saying you want all sheet calculations to complete first, then the
Worksheet_Change event to fire last? (But the calculations are firing it
prematurely?) Yea? Nay?

Maybe you can re-fire the Worksheet_Change event after the
Application.EnableEvents = True statement. I don't know how to do that.
Tom? Jim? Chip?

Or you may need to rewrite your logic to move or duplicate what it is you
are doing in the Worksheet_Change sub to your main code.

That's about all I can think of.

"Kris" wrote:

> Charlie wrote:
> > Maybe try
> >
> > Application.EnableEvents = False
> > Application.Calculate
> > Application.EnableEvents = True
> >
>
> No.
> I doesn't work. Still the same.
>
>
>

How can a calculate fire a Change event. Are there DDE links?

More likely a change event fires a calculate.

--
Regards,
Tom Ogilvy

"Charlie" <Charlie@discussions.microsoft.com> wrote in message
news:6E85DE22-603E-46FA-BB29-D706754CFB67@microsoft.com...
> Are saying you want all sheet calculations to complete first, then the
> Worksheet_Change event to fire last? (But the calculations are firing it
> prematurely?) Yea? Nay?
>
> Maybe you can re-fire the Worksheet_Change event after the
> Application.EnableEvents = True statement. I don't know how to do that.
> Tom? Jim? Chip?
>
> Or you may need to rewrite your logic to move or duplicate what it is you
> are doing in the Worksheet_Change sub to your main code.
>
> That's about all I can think of.
>
> "Kris" wrote:
>
> > Charlie wrote:
> > > Maybe try
> > >
> > > Application.EnableEvents = False
> > > Application.Calculate
> > > Application.EnableEvents = True
> > >
> >
> > No.
> > I doesn't work. Still the same.
> >
> >
> >

Tom Ogilvy wrote:
> How can a calculate fire a Change event.

You are right calculate doesn't fire worksheet_change, but it doesn't
change my situation


>
> More likely a change event fires a calculate.

in automatic calculation yes

in manual you must do this manualy by calling application.calculate or
activesheet.calculate or similar.
but the problem is if code in worksheet_change needs updated data.
There is no way to wait until calculation is finished.

It would be easier to help if I could see your change sub section but this is a example of one. You have to be careful or you can make an endless loop where the user makes a change and then the application makes a change which fires the change event again....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual
    ' Code to execute for processing of user data
    ' I use this area to add formulas and
    ' validate user inputs prior to accepting them
    Application.Calculate
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomatic

End Sub

Originally Posted by Kris

Tom Ogilvy wrote:
> How can a calculate fire a Change event.

You are right calculate doesn't fire worksheet_change, but it doesn't
change my situation


>
> More likely a change event fires a calculate.

in automatic calculation yes

in manual you must do this manualy by calling application.calculate or
activesheet.calculate or similar.
but the problem is if code in worksheet_change needs updated data.
There is no way to wait until calculation is finished.

CodeJunky wrote:
> It would be easier to help if I could see your change sub section but
> this is a example of one. You have to be careful or you can make an
> endless loop where the user makes a change and then the application
> makes a change which fires the change event again....


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("RangeName")) Is Nothing Then
ActiveSheet.Calculate
CollectData
End If


CollectData simply copy data from other sheets and put into this one.
Three nested loops.
CollectData calls CollectSegmentData with different paramters

SheetsName is a list of sheets with data
SegmentName is a name of range with data to copied
DestinationName is a range on active sheet where data should go

number of columns in DestinationName and SegmentName are the same

number of rows of destinationName is defined by NumberOfSegmentsVisible
and equals size of SheetsName(s).




Sub CollectSegmentData(SheetsName As String, SegmentName As String,
DestinationName As String, NumberOfSegmentsVisible As Integer)

Dim iRows As Integer
Dim iCols As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim Sh As Worksheet

iCols = Range(DestinationName).Columns.Count
Range(DestinationName).ClearContents
For iRow = 1 To NumberOfSegmentsVisible
If Not IsError(Range(SheetsName)(iRow).Value) Then
Dim wksName As String
wksName = Range(SheetsName)(iRow).Value
Dim wks As Worksheet
Set wks = ActiveWorkbook.Sheets(wksName)
For iCol = 1 To iCols
Dim SourceValue As Double
SourceValue = wks.Range(SegmentName)(1, iCol).Value
Dim DestCell As Range
Set DestCell = Range(DestinationName)(iRow, iCol)
DestCell.Value = SourceValue
Next iCol
End If
Next iRow
Exit Sub





The problem is that CallectData is fired before calculation is finished.
It doesn't happen on simple examples. it happens on this one.
usually
DestCell.Value = SourceValue
fails with an error.
I don't know why by destination sheet can't be modified.

Charlie wrote:


> Are saying you want all sheet calculations to complete first, then the
> Worksheet_Change event to fire last? (But the calculations are firing it
> prematurely?) Yea? Nay?

Worksheet_change is fired by a user who changes something on a sheet.
Because calculation is set to manual I must recalculate sheet.

Calcualate can fire worksheet_change once again, but I exit it if target
is out of specific region. It can happen when calcuate change something
of that sheet. But it doesn't and I quit worksheet_change if this is out
of region I am interested in.


application.calculate doesn't wait.
so the rest of code doesn't make sense, because data is not updateed on
the rest of sheets.



>
> Or you may need to rewrite your logic to move or duplicate what it is you
> are doing in the Worksheet_Change sub to your main code.

The only thing which I can do is to remove the rest of code from
worksheet_change, put it as a separate procedure and add button to
worksheet. Now user can finish code manualy, but I don't want to do that.

It's like a circle: can't calculate without data, can't get data without
calculating. Sounds like it's time to re-examine the logic flow and design a
new method of solving the problem.

"Kris" wrote:

> Charlie wrote:
>
>
> > Are saying you want all sheet calculations to complete first, then the
> > Worksheet_Change event to fire last? (But the calculations are firing it
> > prematurely?) Yea? Nay?
>
> Worksheet_change is fired by a user who changes something on a sheet.
> Because calculation is set to manual I must recalculate sheet.
>
> Calcualate can fire worksheet_change once again, but I exit it if target
> is out of specific region. It can happen when calcuate change something
> of that sheet. But it doesn't and I quit worksheet_change if this is out
> of region I am interested in.
>
>
> application.calculate doesn't wait.
> so the rest of code doesn't make sense, because data is not updateed on
> the rest of sheets.
>
>
>
> >
> > Or you may need to rewrite your logic to move or duplicate what it is you
> > are doing in the Worksheet_Change sub to your main code.
>
> The only thing which I can do is to remove the rest of code from
> worksheet_change, put it as a separate procedure and add button to
> worksheet. Now user can finish code manualy, but I don't want to do that.
>
>

Charlie wrote:
> It's like a circle: can't calculate without data, can't get data without
> calculating. Sounds like it's time to re-examine the logic flow and design a
> new method of solving the problem.
>
It's not a circle. There is no loop there.
Summary sheet collect data from other sheets and other sheet doesn't
need any data from summary sheet to be calculated.