excel VBA code not working

Question

I have a excel sheet with a VBA code as follows

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 5 Then
        Dim iRet As Integer
        If Not IsEmpty(Range("AZ1").Value) Then
            iRet = MsgBox("You have already selectd a Size Template", _
                          vbOKOnly, "Select Size Template")
            Exit Sub
        End If

        Dim arr As Variant
        arr = Split(Target, ",")
        Range("R14:AZ14").ClearContents
        Range("R14:AZ14").NumberFormat = "@"
        Range("R14", Cells(14, UBound(arr) + 18)) = WorksheetFunction.Transpose( _
                                        WorksheetFunction.Transpose(arr))
        Range("AZ1").Value2 = Target
    End If
End Sub

I saved the excel file as .xlsm(macro enabled excel file) and opend in another.This code works really fine in my machine. BUt not in any other machine. I enabled the marco and allowed the Trust acess to the VBA ojbect model. CAn anybody figure out the issue here . Excel versions are also same in both mahcines

Answer 1

I want to elaborate a bit the comment of mine which was correct suggestion.

First, let me repeat that- you need to switch on events in this way

Application.EnableEvents = true

which you can be run once in Immediate Window in VBA/IDE Editor. Now we know that was it!

Second, if you decided to switch on events using any other subroutine (or event, which is however strange) please keep in mind that there could be some other subroutines, functions or add-ins which require events to be switched off. As long as you are not sure why events are not working you should keep them not working right after your macro doesn't need them any more. Therefore, my suggestion is to switch events off each time you will close your file. Therefore you could add this event to ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = False
End Sub

Extra tip. The best option would be to read events status at the beginning, keep this information until you close your file. You could do it in the following steps:

A) declare public variables in your file

Public boEventsStatus as Boolean

B) read status when opening file (you need to figure it out where put this line of code)

boEventsStatus = Application.EnableEvents

C) switch on events as described at the beginning

D) use this BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = boEventsStatus
End Sub