Solved Worksheet Change event not running on other computers

September 15, 2018 at 05:53:55
Specs: Windows 7
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

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

message edited by mecerrato


See More: Worksheet Change event not running on other computers

Reply ↓  Report •

#1
September 15, 2018 at 06:27:29
Are macros enabled on the other computers? If I am not mistaken macros are disabled by default to prevent malicious code from running without the user's express permission.

You cannot enable macros with a macro, the setting must be set by the user or someone authorized to change securities settings within the environment, i.e. the IT department. It all depends on the permissions on each machine as to whether macros can be enabled locally.

See here:

https://support.office.com/en-us/ar...

In addition, I see that you have put a password in the code. I see a password to unprotect the sheet, but I don't see one to protect it again. Without a password in the Protect instruction, the sheet will be protected, but no password will be needed.

In addition again: Unless you protect the VBA code and hide it from view, a savvy user will be able to find the password and unprotect the sheet whenever they want.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
September 15, 2018 at 08:45:23
Thanks DerbyDad03 for the reply, yes all macros are enabled, both the protect and unprotect are temporarily commented out while I work this issue out.

Reply ↓  Report •

#3
September 15, 2018 at 09:21:03
✔ Best Answer
DerbyDad03 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.

Reply ↓  Report •
Related Solutions


Ask Question