VBA停止计算单元格 [英] VBA Stop calculation of cells

问题描述

所以，用户需要输入一个货币价值一个单元格，让我们说D16，所以我以为我会挂钩工作表上的_Change事件，效果非常好。

但是，我需要工作表的其余部分当一个条目已经提交到D16中时，基本上输入500000时，其他单元格将被更新为另一个工作表中的值。

我的代码

  Private Sub Worksheet_Change（ByVal Target As Range）
 
如果Target = Range（D16）然后
 
 Dim numeric 
 numeric = IsNumeric（Target）
 
 
如果numeric = False然后
 
 MsgBoxerror
退出Sub 
 
 ///这是我需要停止计算从
 
结束如果
 
结束如果
 
 End Sub 
  

解决方案

我希望下面的代码有帮助。您需要将其粘贴到表单代码部分。

  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 ）
 
如果不相交（目标，rng）不是和IsNumeric（目标）然后
 
如果Target.Value> = 500000 Then 
 MsgBoxValue大于500000
如果
 
 
结束If 
 
 
 Application.EnableEvents = True 
 Application.Calculation = xlCalculationAutomatic 
 End Sub 
  

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

解决方案

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

这篇关于VBA停止计算单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！