VBA Excel-Workbook_SheetChange [英] VBA Excel - Workbook_SheetChange
问题描述
希望这个问题还没有被问到,我试图寻找答案,却找不到任何东西.
Hopefully this question hasn't already been asked, I tried searching for an answer and couldn't find anything.
这可能是一个简单的问题,但是我在excel中编写了我的第一个宏,但遇到了无法找到解决方案的问题.我写了一些宏,这些宏基本上根据同一行的另一列中的值动态地对列进行求和(以便行数可以更改并且公式自动向下移动),然后从事件
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
.
我遇到的问题是,我从宏中更改单元格的值以显示总和的结果,然后再次调用Workbook_SheetChange
,这是我不希望的.现在它可以工作,但是我可以对其进行跟踪并看到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_SheetChange
again, which I do not want. Right now it works, but I can trace it and see that Workbook_SheetChange
is being called multiple times. This is preventing me from adding other cell changes to the macros, because then it results in an infinite loop.
我希望每次对工作表进行更改时都运行宏,但是我看不到允许宏更改单元格值的任何方法,因此我不知道该怎么做.如果有帮助,我将在下面粘贴我的代码.
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.
推荐答案
使用Application.EnableEvents
阻止Excel调用事件过程.
Use Application.EnableEvents
to prevent Excel from calling event procedures.
放入
Application.ScreenUpdating = False
在代码开头
和Application.ScreenUpdating = True
结束.
Put
Application.ScreenUpdating = False
at the beginning of code
and Application.ScreenUpdating = True
at the end.
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
这篇关于VBA Excel-Workbook_SheetChange的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!