Manual calculation with message box

Donbozone

New Member
Joined
Mar 28, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
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

New Member
Joined
Mar 28, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
I did, but not sure what I'm missing because nothing happens.

This is what I already have:

VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
post the code behind Calculate Now button
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
Now I understand what you are doing
I thought you had created a special button
Will post code later :)
 

Donbozone

New Member
Joined
Mar 28, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 28, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Yes, it will be set to manual calculation and that is my main goal.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
In that case the code I gave you earlier should work

Worksheet calculate.jpg



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

VBA Code:
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()
 

Watch MrExcel Video

Forum statistics

Threads
1,118,814
Messages
5,574,487
Members
412,597
Latest member
Timtec
Top