VBA Stop calculation of cells

Question

Very new to VBA in Excel, got asked to do some validation on cell change and got a bit stuck.

So, user needs to enter a monetary value into a cell, let's say D16, so I thought I'd hook into the _Change event on the Worksheet which works quite well.

However, I need the rest of the worksheet to not complete the calculation when an entry has been submitted into D16, basically, when 500000 is entered, other cells gets updated with values from another worksheet.

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

@Santosh, yes, neither of these solved our problem. We ended up hooking up each event available and calling the Worksheet_Change event in each of them. Weird scenario, I know, I cannot explain why, but it worked. — JadedEric, Jun 10 '13 at 16:19

score 21 · Accepted Answer · answered Jun 06 '13 at 13:03

I hope below code helps. You need to paste this in sheet code section.

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

score 11 · Answer 2 · answered Jun 06 '13 at 13:00

11

Use Application.Calculation = xlCalculationManual.

Don't forget to switch it back on again: Application.Calculation = xlCalculationAutomatic.

answered Jun 06 '13 at 13:00

Bathsheba

231,907
34
361
483

VBA Stop calculation of cells

2 Answers2

Linked