如何结束无限的“改变”在VBA中循环 [英] How to end infinite "change" loop in VBA

问题描述

我有一个视觉基础的问题。我想制作一个宏/函数，它会将我输入的数字乘以3，并在同一个单元格中给出一个结果。我尝试过这样的东西：

  Sub Worksheet_Change（ByVal Target As Range）
如果Target.Address =$ Q $ 21然后
 Target.Value = Target.Value * 3 
 End If 
 End Sub 
  

但它不工作 - 我得到的结果像xE25，因为它不断乘以。


我希望在第一次迭代后停止，或者只有当我按下输入，而不是单元格中的每个变化时才能停止。

很容易将导致不同的单元格，但这不是我的观点。


-----编辑：

我编辑If行到：

If（Target.Column = 5 or Target.Column = 11或Target.Column = 17或Target.Column = 23）And（Target.Row> = 19 And Target.Row< = 24） Target.Value<> 然后
所以它将适用于我需要的所有单元格。之后，最好的解决方案是@ Chrismas007给出的方式，因为当尝试一次删除几个单元格中的数据时，它不会提示错误。

解决方案

使用错误处理来确保 .EnableEvents 返回到 True ：

  Sub Worksheet_Change（ByVal Target As Range）
错误GoTo CleanExit 
如果Target.Address =$ Q $ 21然后
 Application.EnableEvents = False 
 Target.Value = Target.Value * 3 
 End If 
 CleanExit：
 Application.EnableEvents = True 
 On Error GoTo 0 
 End Sub 
  

I have a problem with visual basic. I want to make a macro/function that will multiply a number I enter by 3 and give a result in the same cell. I tried something like this:

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$Q$21" Then
        Target.Value = Target.Value * 3
    End If
End Sub

but it doesn't work - I'm getting results like "xE25" because it keeps multiplying.

I'd like it to stop after first iteration or work only when I press "enter" not with every change in the cell.
It's quite easy to put a result in different cell, but it's not my point.


-----Edit:
I edited "If" line to :
If (Target.Column = 5 Or Target.Column = 11 Or Target.Column = 17 Or Target.Column = 23) And (Target.Row >= 19 And Target.Row <= 24) And Target.Value <> "" Then
so it would work on all cells that I need. After that, the best solution is the way given by @Chrismas007, because it doesn't prompt an error when trying to delete data in few cells at once.

解决方案

With error handling to ensure .EnableEvents goes back to True:

Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    If Target.Address = "$Q$21" Then
        Application.EnableEvents = False
        Target.Value = Target.Value * 3
    End If
CleanExit:
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

这篇关于如何结束无限的“改变”在VBA中循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！