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:
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?
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:
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
Any other ideas to trigger a recalculation?