How can we wait for specific sheet to calculate?

[ballgnm]

Hello, I'm making work related excel macro and intead of using Application.Calculation = xlCalculationManual, I'm using Sheets("sheetname").EnableCalculation = False to avoid "You broke something" kind of words. Because if it crashes or stops unintended way when calculation was set to manual, it stays that way for whole Excel program.
It's used by many people so I don't want to access their computers or go to their rooms everytime their excel calculation has turned into Manual so I have to make it automatic manually.

But later doesn't seem to calculate automatically and I can't tell if my calculation is done or not.
Because of that calculation-heavy sheets are not calculated properly.

Application.CalculationState always gives me xlDone state as 0. So checking this after Sheets("sheetname").EnableCalculation = true doesn't really help.

Sheets("sheetname").EnableCalculation = True
Sheets("sheetname").Calculate
Debug.Print "sheetname: " & CStr(Application.CalculationState) 'gives me "sheetname: 0" even when calculation isn't finished yet.
Sheets("sheetname").EnableCalculation = False

Is there any way to check if calculation process has ended?

Sheets have lot of formulas. It takes a lot of time to calculate so I have to disable calculation
similar to this thread VBA to ensure that workbook/worksheet(s) are calculated fully?
But there doesn't seem to have actual solution there.

 

[Micron]

The solution is posted 3 times - use Do Events, although it is the link in the thread you linked to that indicates that worked.

Do Until Application.CalculationState = xlDone
   DoEvents
Loop

or this

If Not Application.CalculationState = xlDone Then
    DoEvents
End If

or this

Do
    DoEvents
    Application.Calculate
Loop While Not Application.CalculationState = xlDone

or perhaps move some code to the Application.AfterCalculation event

 

[ballgnm]

Hello, and thank you for your answer
It was condition check that was preventing the calculation.
It was my fault for not noticing it
Code was more like

If condition = true then
    'bunch of lines of code
    Sheets("sheetname").EnableCalculation = True
    Sheets("sheetname").Calculate
    Debug.Print "sheetname: " & CStr(Application.CalculationState)
    Sheets("sheetname").EnableCalculation = False
    'bunch of lines of code
End If
Debug.Print "sheetname: " & CStr(Application.CalculationState)

I couldn't find "delete thread" or "edit thread" button after I found my mistake.
I'm sorry for misleading information for that.

 

[Micron]

NP. Just mark your latest post as the solution if you didn't use anyone else's solution.