VBA Excel - Workbook_SheetChange

VBA Excel - Workbook_SheetChange

提问by user2947014

Hopefully this question hasn't already been asked, I tried searching for an answer and couldn't find anything.

希望这个问题还没有被问过，我试图寻找答案但找不到任何东西。

This is probably a simple question, but I am writing my first macro in excel and am having a problem that I can't find out a solution to. I wrote a couple of macros that basically sum up columns dynamically (so that the number of rows can change and the formula moves down automatically) based on a value in another column of the same row, and I call those macros from the event Workbook_SheetChange.

这可能是一个简单的问题，但我正在用 excel 编写我的第一个宏，但遇到了一个找不到解决方案的问题。我编写了几个宏，它们基本上根据同一行的另一列中的值动态汇总列（以便行数可以改变并且公式自动向下移动），并且我从 event 调用这些宏Workbook_SheetChange。

The problem I'm having is, I change a cell's value from my macro to display the result of the sum, and this then calls Workbook_SheetChangeagain, which I do not want. Right now it works, but I can trace it and see that Workbook_SheetChangeis being called multiple times. This is preventing me from adding other cell changes to the macros, because then it results in an infinite loop.

我遇到的问题是，我从宏中更改单元格的值以显示总和的结果，然后Workbook_SheetChange再次调用，这是我不想要的。现在它可以工作，但我可以跟踪它并看到它Workbook_SheetChange被多次调用。这阻止我向宏添加其他单元格更改，因为它会导致无限循环。

I want the macros to run every time a change is made to the sheet, but I don't see any way around allowing the macros to change a cell's value, so I don't know what to do. I will paste my code below, in case it is helpful.

我希望宏在每次对工作表进行更改时都运行，但我看不到允许宏更改单元格值的任何方法，所以我不知道该怎么做。我将在下面粘贴我的代码，以防万一。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Row As Long
    Dim Col As Long
    Row = Target.Row
    Col = Target.Column
    If Col <> 7 Then
        Range("G" & Row).Select
        Selection.Formula = "=IF(F" & Row & "=""Win"",E" & Row & ",IF(F" & Row & "=""Loss"",-D" & Row & ",0))"
        Target.Select
    End If
    Call SumRiskColumn
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call SumOutcomeColumn
End Sub

Sub SumOutcomeColumn()
    Dim N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    Cells(N + 1, "G").Formula = "=SUM(G2:G" & N & ")"
End Sub

Sub SumRiskColumn()
    Dim N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    Dim CurrTotalRisk As Long
    CurrTotalRisk = 0
    For i = 2 To N
        If IsEmpty(ActiveSheet.Cells(i, 6)) And Not IsEmpty(ActiveSheet.Cells(i, 1)) And Not IsEmpty(ActiveSheet.Cells(i, 2)) And Not IsEmpty(ActiveSheet.Cells(i, 3)) Then
            CurrTotalRisk = CurrTotalRisk + ActiveSheet.Cells(i, 4).Value
        End If
    Next i
    Cells(N + 1, "D").Value = CurrTotalRisk
End Sub

Thank you for any help you can give me! I really appreciate it.

谢谢你能给我的任何帮助！对此，我真的非常感激。

回答by Santosh

Use Application.EnableEventsto prevent Excel from calling event procedures.

使用Application.EnableEvents以防止Excel调用事件过程。

Put Application.ScreenUpdating = Falseat the beginning of code and Application.ScreenUpdating = Trueat the end.

放在 Application.ScreenUpdating = False代码的开头和Application.ScreenUpdating = True结尾。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Dim Row As Long
    Dim Col As Long
    Row = Target.Row
    Col = Target.Column
    If Col <> 7 Then
        Range("G" & Row).Select
        Selection.Formula = "=IF(F" & Row & "=""Win"",E" & Row & ",IF(F" & Row & "=""Loss"",-D" & Row & ",0))"
        Target.Select
    End If
    Call SumRiskColumn
    Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Application.EnableEvents = False
    Call SumOutcomeColumn
     Application.EnableEvents = True
End Sub