none
Worksheet Change event not running on other computers RRS feed

  • Question

  • I have an excel sheet where I wrote the below code to run whenever any of the defined cells change. The sheet works perfectly on the computer I created the sheet in but when I took it to a customer site using their computer it did not work.

    Not sure what changes I have to make to make it work, I read a few articles that speak about the application.enableevents code and I tried placing it in a few spots but it didn't work, any suggestions?

    I also did a whole project search for Application.EnableEvents and don't have that anywhere in the project. I checked the immediate window and typed ?Application.EnableEvents and it came back as TRUE.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Address = "$D$7" Then              'loan type
            Call Calc_MI
        End If
        If Target.Address = "$D$8" Then              'credit score
            Application.EnableEvents = True
            Call Calc_MI
        End If
        If Target.Address = "$D$9" Then              'more than 1 borrower
            Call Calc_MI
        End If
        If Target.Address = "$C$12" Then             'income
            Call Calc_MI
        End If
        If Target.Address = "$D$21" Then
            Call Calc_MI
        End If
        If Target.Address = "$D$21" Then             'sales price
            Call Calc_MI
        End If
        If Target.Address = "$D$23" Then             'loan amount
            Call Calc_MI
        End If
        If Target.Address = "$H$4" Then              'taxes
            Call Calc_MI
        End If
        If Target.Address = "$H$5" Then              'insurance
            Call Calc_MI
        End If
        If Target.Address = "$H$6" Then              'HOA
            Call Calc_MI
        End If
        If Target.Address = "$H$7" Then              'term
            Call Calc_MI
        End If
        If Target.Address = "$H$8" Then              'rate
            Call Calc_MI
        End If
        If Target.Address = "$H$17" Then             'car payment
            Call Calc_MI
        End If
        If Target.Address = "$H$18" Then             'credit cards/loans
            Call Calc_MI
        End If
    
        If Range("H13").Value > Range("H11").Value Then
            ActiveSheet.Shapes("HousingX").Visible = True
            ActiveSheet.Shapes("HousingCheck").Visible = False
        End If
        If Range("H13").Value <= Range("H11").Value Then
            ActiveSheet.Shapes("HousingX").Visible = False
            ActiveSheet.Shapes("HousingCheck").Visible = True
        End If
    
        If Range("H14").Value > Range("H12").Value Then
            ActiveSheet.Shapes("DTIX").Visible = True
            ActiveSheet.Shapes("DTICheck").Visible = False
        End If
        If Range("H14").Value <= Range("H12").Value Then
            ActiveSheet.Shapes("DTIX").Visible = False
            ActiveSheet.Shapes("DTICheck").Visible = True
        End If
    
    End Sub

    Here is the Calc_MI routine:

    Sub Calc_MI()
    
        'ActiveSheet.Unprotect Password:="Mortgage1"
    
        If Range("D7").Value = "FHA" Then
            Range("H9").Value = 0.85
        Else
            If Range("E24").Value < 0.8001 Or Range("D7").Value = "VA" Then
                Range("H9").Value = ""
            Else
                If Range("H14").Value > 0.45 Then
                    Range("H9").Value = (Sheets("Closing Costs").Range("BM100").Value +
                    Sheets("Closing Costs").Range("BM101").Value + Sheets("Closing
                    Costs").Range("BM102").Value)
                Else
                    Range("H9").Value = (Sheets("Closing Costs").Range("BM100").Value +
                    Sheets("Closing Costs").Range("BM102").Value)
                End If
            End If
        End If
    
        'ActiveSheet.Protect
    
    End Sub


    MEC

    Saturday, September 15, 2018 1:16 PM

All replies

  • It sounds like macros are disabled on the other users' computers.

    They have to make sure that macros aren't completely disabled in File > Options > Trust Center > Trust Center Settings... > Macro Settings, and that they allow macros when they open the workbook.

    You can shorten

        If Target.Address = "$D$7" Then              'loan type
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$D$8" Then              'credit score
            Application
    .EnableEvents = True
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$D$9" Then              'more than 1 borrower
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$C$12" Then             'income
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$D$21" Then
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$D$21" Then             'sales price
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$D$23" Then             'loan amount
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$H$4" Then              'taxes
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$H$5" Then              'insurance
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$H$6" Then              'HOA
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$H$7" Then              'term
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$H$8" Then              'rate
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$H$17" Then             'car payment
           
    Call Calc_MI
       
    End If
       
    If Target.Address = "$H$18" Then             'credit cards/loans
           
    Call Calc_MI
       
    End If

    to

        If Not Intersect(Range("C12,D7:D9,D21,D23,H4:H8,H17:H18"), Target) Is Nothing Then
            Application.EnableEvents = False
            Call Calc_MI
            Application.EnableEvents = True
        End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Lina-MSFT Thursday, September 20, 2018 2:41 AM
    • Unproposed as answer by Lina-MSFT Friday, September 21, 2018 9:54 AM
    Saturday, September 15, 2018 2:37 PM
  • Thanks for the code efficiency tip but the macros are all enabled and I even trusted the VBA objects. This same sheet works perfectly fine on my computer where I developed it.

    MEC

    Saturday, September 15, 2018 3:50 PM
  • I found the problem and it is completely unrelated to anything I would have ever thought. The problem had to do with the date picker addon I had on the computer I wrote the sheet in. The other computers do not have this and whenever I opened the sheet on any other computer it would be stuck in design mode where it cannot execute macros. I removed that pesky date picker and resent sheet and all is working back to normal. Sheesh I have had this issue for 3 days now.

    MEC

    Saturday, September 15, 2018 5:19 PM
  • Thanks for sharing the cause of the problem.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, September 15, 2018 6:28 PM
  • Thanks for your response. Please remember to mark your reply as answer if it helped. Please help us close the thread. Thank you for understanding.

    If you have any question, or update, please feel free to let me know.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, September 17, 2018 2:26 AM
    Moderator