CODEWITHSUNDEEP
excelvba
Phil
Phil

Reputation: 1

VB stops working after I save file

New to excel

When I add the following VB to a sheet it works:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set KeyCells = Range("A1:C10")
    Set isect = Application.Intersect(KeyCells, Range(Target.Address))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        isect.Value = isect.Value - 40
        Application.EnableEvents = True
    End If
End Sub

Once I save it as xlsm, it no longer works.

If I close and re-open it still does not work.

Need it to work after I save/re-open.

Upvotes: 0

Views: 2473

Answers (2)

Val
Val

Reputation: 325

I just run into this same issue, and after some hours I found a solution: you should save the file in a trusted location.

You can find the trusted locations list in File > Options > Trust Center > Trust Center Settings > Trusted Locations.

Either you save the file in one of the listed locations or you add a new location.

Upvotes: 0

Ralph J
Ralph J

Reputation: 478

Sounds like you have a security setting that is preventing the VBA code from running when you open a .xlsm file. In Excel 2010, those settings are on the Developer tab, to the left with a yellow triangle icon and "Macro Security". The top option there is to "Disable all macros without notification," which is what seems to be happening to you. The next one down, disable with notification, allows you to override that choice when the file opens and say in effect, "no, I want to run the macros in THIS file this time."

If you can't find that page but can get to the "Trust Center," look for "Macro Settings" within that, and that page brings up what I described above.

Very quick way to check that the macro runs is to put a statement like

Msgbox "Hi"

at the top of your routine. If you see that after allowing the macros to run, then you know that the routine ran; if nothing else happens then there is an issue in the code, but not with the macro execution.

Upvotes: 1

Related Questions