CODEWITHSUNDEEP
excelvba
Zack E
Zack E

Reputation: 706

Automatic Worksheet Calculating cell value change not working

I am running into an issue with TextBoxes on a UserForm and also two cells on a different sheet in the same workbook updating automatically because the Worksheet_Change(ByVal Target as Range)is not running calculations automatically. Below is the current code that does not work and im thinking its because of the If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing. I do not have Application.EnableEvents anywhere that would have an effect on these cells or the TextBoxes the code below would reference. Any assistance would be greatly appreciated.

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

Upvotes: 2

Views: 425

Answers (1)

Zack E
Zack E

Reputation: 706

Thank you all for your ideas and assistance, but I decided to take the formulas out of the sheet. I created a sub inside a module and then called the sub from the Worksheet_Change event and it works now. I'm sure that the code could be a lot cleaner, but for now it works.

Module Code:

 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

Code in Worksheet module:

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

Upvotes: 1

Related Questions