Manual calculation with message box

[Donbozone]

Hi Everyone.

I would need a pieace of simple code which will be trigered by pressing Calculate Now button (manual calculation).

What I need after that is a message box with 2 conditions:

1. "Calculation successful" (if there is no cells with error on that sheet)

2. "Calculation unsuccessful" (if there is even one cell with error value - N/A, #Value etc.)

I believe this is possible since I already have message box after calculation is done, but I miss check on error values after that.

Thanks in advance

 

[Yongle]

Try

Sub ReCalc()
    Dim rng As Range
    Application.Calculate
    On Error Resume Next
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16)
    On Error GoTo 0
    If Not rng Is Nothing Then MsgBox "Not successful" & vbCr & rng.Address(0, 0) Else MsgBox "Successful"
End Sub

 

[Donbozone]

I did, but not sure what I'm missing because nothing happens.

This is what I already have:

Private Sub Worksheet_Calculate()
MsgBox "Calculation successful"

Do
Loop Until Application.CalculationState = xlDone
End Sub

..and it works, but it does not check whether there are cells with error.

 

[Yongle]

post the code behind Calculate Now button

 

[Donbozone]

You mean on form button or on built in Calculate Now (on Formula tab) - which I was referring to ?

I'm really not sure how to do that.

 

[Yongle]

Now I understand what you are doing
I thought you had created a special button
Will post code later

 

[Donbozone]

No, no..I wanted to use built in "Calculate now" and just to get confirmation message that "Calculation was succesfull" if there is no cells with error on that sheet or "Calculation was unssuccessful" if there is any cell with error (#value, N/A, Div/0 etc) and refferal to that cell if possible

 

[Yongle]

Worksheet_Calculate is not only triggered when you press that button ... it is also triggered every time Excel decides to recalculate ... and there may be a chain of triggers depending on what is happening in your worksheet
Are calculations set to manual in the workbook ?

 

[Donbozone]

Yes, it will be set to manual calculation and that is my main goal.

 

[Yongle]

In that case the code I gave you earlier should work

What you need to do
Delete your version of WorkSheet_Calculate and replace with code below

Private Sub Worksheet_Calculate()
    Dim rng As Range
    On Error Resume Next
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16)
    On Error GoTo 0
    If Not rng Is Nothing Then MsgBox "Not successful" & vbCr & rng.Address(0, 0) Else MsgBox "Successful"
End Sub

NOTE
The procedure will not be triggered unless there is something new to calculate
To ensure that the procedure is triggered every time you click on Calculate Now ...
Place this formula in any cell in the worksheet
=NOW()