excel - 自动工作表计算单元格值更改不起作用

Question

我遇到了用户窗体上的文本框以及同一工作簿中不同工作表上的两个单元格自动更新的问题，因为 Worksheet_Change(ByVal Target as Range)没有自动运行计算。以下是当前不起作用的代码，我认为它是因为 If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing .我没有Application.EnableEvents任何会对这些单元格或下面的代码将引用的文本框产生影响的地方。任何帮助将不胜感激。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wb As Workbook
    Dim wspGen As Worksheet
    Dim KeyCells As Range, genLTV As Range, genCLTV As Range

    Set KeyCells = Range("Loan_Amount, Estimated_Value, Purchase_Price, Total_Other_Mtg, Additional_Collateral, LTV, CLTV")
    Set genLTV = wspGen.Range("GenLTV")
    Set genCLTV = wspGen.Range("GenCLTV")

    'I believe this is looking to see if all cell values in this range have changed and not the individual cell value has changed
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then  
        genLTV = LTV
        genCLTV = CLTV
        LP.LTV = LTV   'Userform TextBox
        LP.CLTV = CLTV 'Userform TextBox
        Call LTVFormat
    End If

End Sub

Answer 1

谢谢大家的想法和帮助，但我决定把公式从表格中拿出来。我在一个模块中创建了一个 sub，然后从 Worksheet_Change 调用 sub事件，它现在工作。我确信代码可能会更干净，但现在它可以工作。

模块代码:

 Sub LTVCalcs()
    Dim wb As Workbook
    Dim wsSI As Worksheet, wspGen As Worksheet
    Dim lAmount As Range, pPrice As Range, eVal As Range, oMtg As Range, oColl As Range, LTV As Range, CLTV As Range, genLTV As Range, genCLTV As Range, keyCells As Range
    Dim x As Integer

    Set wb = Application.ThisWorkbook
    Set wsSI = wb.Sheets("SavedInfo")
    Set wspGen = wb.Sheets("pGeneralInfo")
    Set lAmount = wsSI.Range("Loan_Amount")
    Set pPrice = wsSI.Range("Purchase_Price")
    Set eVal = wsSI.Range("Estimated_Value")
    Set oMtg = wsSI.Range("Total_Other_Mtg")
    Set oColl = wsSI.Range("Additional_Collateral")
    Set LTV = wsSI.Range("LTV")
    Set CLTV = wsSI.Range("CLTV")
    Set genLTV = wspGen.Range("GenLTV")
    Set genCLTV = wspGen.Range("GenCLTV")

    Set keyCells = wsSI.Range("Loan_Amount,Purchase_Price,Estimated_Value,Total_Other_Mtg,Additional_Collateral")
    oMtg = Application.WorksheetFunction.Sum(wsSI.Range("_2nd_Mtg_Amount"), wsSI.Range("_3rd_Mtg_Amount"), wsSI.Range("_4th_Mtg_Amount"), wsSI.Range("_5th_Mtg_Amount"))

    If lAmount = vbNullString Then lAmount = 0
    If pPrice = vbNullString Then pPrice = 0
    If eVal = vbnulsltring Then eVal = 0
    If oMtg = vbNullString Or oMtg = 0 Then oMtg = 0
    If oColl = vbNullString Then oColl = 0
    If LTV = vbNullString Then LTV = 0
    If CLTV = vbNullString Then CLTV = 0

    If eVal <= pPrice Then
        x = 1
    Else: x = 2
    End If

    Application.EnableEvents = False

    For Each cell In keyCells
        If cell.Value >= 0 Then Application.EnableEvents = True
            Select Case x
                Case Is = 1
                    LTV = lAmount / (eVal + oColl)
                    CLTV = (lAmount + oMtg) / (eVal + oColl)
                    LP.LTV = LTV
                    LP.CLTV = CLTV
                    genLTV = LTV
                    genCLTV = CLTV
                Case Is = 2
                    LTV = lAmount / (pPrice + oColl)
                    CLTV = (lAmount + oMtg) / (pPrice + oColl)
                    LP.LTV = LTV
                    LP.CLTV = CLTV
                    genLTV = LTV
                    genCLTV = CLTV
            End Select
    Next cell

    Application.EnableEvents = True

End Sub

工作表模块中的代码:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("Loan_Amount") Then Call LTVCalcs
    If Target.Address = Range("Estimated_Value") Then Call LTVCalcs
    If Target.Address = Range("Total_Other_Mtg") Then Call LTVCalcs
    If Target.Address = Range("Additional_Collateral") Then Call LTVCalcs
    If Target.Address = Range("Purchase_Price") Then Call LTVCalcs
End Sub