Hello,

I have some code that launches from a control button on a sheet. It does a bunch of stuff but at one point it writes a Worksheet_Change() event into the current sheet as follows:
'Write code to worksheet_change() event to automatically update axes
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.codeName).CodeModule
    i = .CreateEventProc("Change", "Worksheet")
    i = i + 2
    .InsertLines i, "With ActiveSheet.ChartObjects(""Gantt"")" & vbCrLf & _
                    "   .Chart.Axes(xlValue).MinimumScale = Range(""C" & tasks + 3 & """).Value" & vbCrLf & _
                    "   .Chart.Axes(xlValue).MaximumScale = Range(""C" & tasks + 6 & """).Value" & vbCrLf & _
                    "End With" & vbCrLf
End With
The ouput of this should write the following code into the activesheet, assuming that access to the VBA project object model is enabled and the reference "Microsfot Visual Basic for Appplications Extensibility 5.3" is included:

Private Sub Works
heet_Change(ByVal Target As Range)

With ActiveSheet.ChartObjects("Gantt")
   .Chart.Axes(xlValue).MinimumScale = Range("C12").Value
   .Chart.Axes(xlValue).MaximumScale = Range("C15").ValueEnd
With

End Sub
Here is my issue. If I have the VBA Editor window open and I either run from the command button in the excel sheet, or directly from the editor itself then the code works fine and the intended result is achieved.
However, if I close the VBA Editor and run from the command button then the Worksheet_Change() event does not get successfully written. All other code runs as intended, it's just this bit that doesn't work for some reason.

Does anyone have any ideas as to why this may be?

Thanks in advance,
-Ralph