Worksheet_Change Question

[RsLewis]

Excel file 1 has worksheet code as follows:Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 1 And Target.Column < 15 Then
Cells(Target.Row, 16) = True
End If

End Sub
I want to, in a macro, open another file, and set the worksheet code to this same sub.
How do I do it?
Thanks

 

[Colo]

Hi RsLewis,
If you want to use this event for all xl files, you need to use class module(as application event).

<Pre>
'//Put this code on Class Module named Class1 on Personal.xls
Public WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column > 1 And Target.Column < 15 Then
Cells(Target.Row, 16) = True
End If
End Sub


'//Put this code on Std Module named on Personal.xls
Public x As New Class1

Sub Autpen()
Set x.App = Application
End Sub
</Pre>

 

[RsLewis]

Colo,
Thanks for that info. If I don't want to do this for all excel files, but only ones I create thru a specific macro, does your answer change?
Thanks, Bob