Recalculate code is crashing Excel (1 Viewer)

[laxster]

Thanks to Brian's help with a tweak, the other bit of code I wrote is mostly working. However, one issue I am having with the code I wrote is that the function doesn't always update despite changes on the sheet without hitting Ctrl-Alt-F9.

Send keys doesn't work, nor do other methods I've found. The only thing that does the trick is the bolded bit of code:

Function previous(ByVal status_x As Range, ByVal bin_x As Range) As String

    For Each C In Sheet6.Range(Range("F9"), Range("F9").End(xlDown)).Cells
    
        If Cells(C.Row, bin_x.Column).Text = bin_x.Text Then
               If Cells(C.Row, status_x.Column).Text = "Hang" Or Cells(C.Row, status_x.Column).Text = "Empty" Then
                    previous = Cells(C.Row, status_x.Column).Value
                    Exit Function
               End If
        End If
        
    Next
    
    previous = status_x.Value
[B]
[I]Dim oSht As Worksheet
Application.Calculation = xlCalculationManual

For Each oSht In Worksheets
oSht.EnableCalculation = False
oSht.EnableCalculation = True
Next oSht

Application.Calculate
[/I]   [/B]
    
End Function

Unfortunately, this bit of code also has a tendency to crash Excel as well. I've been looking at http://www.decisionmodels.com/calcsecretsh.htm , but nothing truly works.

Any other ideas to trigger a recalculation?

 

[Brianwarnock]

I see that you decided to call it previous not update, makes more sense, anyway don't know why it doesn't always recalculate but your sheet is complex, have you tried

Worksheets("sheetname").Calculate

Brian

 

[Brianwarnock]

Did you resolve this?

Brian

 

[laxster]

I ended up using a different macro which calls for the user to manually recalculate the sheet.

 

[Brianwarnock]

Never give in.

I turned auto updating off and used the following in A1 =B1*C1 and sure enough it did not calculate .
Put the code I suggested in the Worksheet change event and yes it worked whenever I change the sheet, hey I thought it knows the sheet so ended up with

Private Sub Worksheet_Change(ByVal Target As Range)
Calculate
End Sub

You can use the worksheets etc code to force calculation on other sheets when this sheet changes

Brian