更改单元格时，excel VBA自动运行宏 [英] excel VBA run macro automatically when a cell is changed

问题描述

通过此链接，我在我的问题中找到了很多答案:

I found a lot of answers to my question following this link: automatically execute an Excel macro on a cell change

我要提出一个新问题的原因是因为我没有足够的声誉来对该线程发表评论，并且我很难将链接的单元格连接到要运行的宏.

The reason I'm opening a new question is because I don't have enough reputation to comment on that thread and I'm having difficulty connecting my linked cell to the macro I want to run.

因此，链接的单元格包含一个公式，仅当我更改其他单元格时，该公式才会更改值.我遇到的问题是，仅当我更改单元格的值而不是公式的值时，我的宏才会运行.我正在寻找一种在公式返回的值更改时激活宏的方法.

So the cells that are linked contains a formula causing it to change value only when I change other cells. The problem I'm having is that my macro only runs if I change the value of the cell and not the value of the formula. I'm looking for a way to activate the macro when the value that the formula returns changes.

到目前为止，这是我的代码:

This is my code so far:

我的单元格范围命名为值"，我想隐藏标签刷新"

My range of cells is named "Values" and I want to hide the label "Refresh"

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Me.Range("Values")) Is Nothing Then Exit Sub
        Application.EnableEvents = False 'to prevent endless loop

        Sheet1.Refresh.Visible = False

        Application.EnableEvents = True
End Sub

很抱歉再次提出另一个问题，但是正如我说的那样，我无法对其他主题发表评论.

Sorry again for opening another question but as I said I couldn't comment on the other thread.

推荐答案

使用此代码.它将值存储到工作表中最后一行\列的单元格中.您需要将值手动存储一次，但是此后它将在每次更改时为您存储该值，以便它可以检查下一次计算工作表的时间(并且公式结果可能会更改).

Use this code. It stores the values into a cell on the worksheet that is the last row\column. You'll need to store the value there manually once, but after that it will store it for you each time there's a change so it can check the next time the sheet is calculated (and the formula result is potentially changed).

Private Sub Worksheet_Calculate()

Dim bOld as Byte
bOld = Me.Cells(Me.Rows.Count,Me.Columns.Count)

If Me.Range("Values") <> bOld Then 

    Application.EnableEvents = False 'to prevent endless loop
    Me.Cells(Me.Rows.Count,Me.Columns.Count).Value = Me.Range("Values").Value 'store new value
    Sheet1.Refresh.Visible = False
    Application.EnableEvents = True

End If

End Sub  

这篇关于更改单元格时，excel VBA自动运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！