VBA Stop Cell Computation

Question

Very new to VBA in Excel, he was asked to do some check on cell change and got a little stuck.

So, the user needs to enter a monetary value in the cell, say, D16, so I thought that I would connect to the _Change event on the Worksheet, which works pretty well.

However, I need the rest of the worksheet to not complete the calculation when the record was sent to D16, basically when 500000 is entered, other cells are updated with values ​​from another sheet.

My code

Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D16") Then Dim numeric numeric = IsNumeric(Target) If numeric = False Then MsgBox "error" Exit Sub /// this is where I need to "stop" the calculations from firing End If End If End Sub 

Answer 1

I hope the code below helps. You need to insert this into the code section of the sheet.

 Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Application.EnableEvents = False Application.Calculation = xlCalculationManual Dim rng As Range Set rng = Range("D16") If Not Intersect(Target, rng) Is Nothing And IsNumeric(Target) Then If Target.Value >= 500000 Then MsgBox "Value is greater than 500000" End If End If Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub 

Answer 2

Use Application.Calculation = xlCalculationManual .

Remember to turn it back on: Application.Calculation = xlCalculationAutomatic .