Setting up Worksheet_Change event
 

Could anybody help me

I wish to include a Worksheet_Change event on a
spreadsheet, so that I can record when the value in a cell
is changed.

I can do this physically by: right clicking on the
worksheet tab, selecting the View code option and then
adding the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("M:M")) Is Nothing Then
Target.Offset(, 1).Value = Date
End If
End Sub

My query is how do I automate the setting up of this event
from a VB macro that creates the Excel worksheet? I wish
to do this to ensure certain functionality and formatting
is included in the worksheet when it is created.

Many thanks for any Help you can give me.

Nigel

Setting up Worksheet_Change event
 

Hi
as a starting point see:
http://www.cpearson.com/excel/vbe.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Nigel Stevens wrote:
Could anybody help me

I wish to include a Worksheet_Change event on a
spreadsheet, so that I can record when the value in a cell
is changed.

I can do this physically by: right clicking on the
worksheet tab, selecting the View code option and then
adding the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("M:M")) Is Nothing Then
Target.Offset(, 1).Value = Date
End If
End Sub

My query is how do I automate the setting up of this event
from a VB macro that creates the Excel worksheet? I wish
to do this to ensure certain functionality and formatting
is included in the worksheet when it is created.

Many thanks for any Help you can give me.

Nigel

Setting up Worksheet_Change event
 

Look here for this and more

http://www.cpearson.com/excel/vbe.htm

The CodeModule object has a method called CreateEventProc that you can use
to create an event procedure in class module, a sheet module, or the
ThisWorkbook module. The advantage of CreateEventProc over InsertLines is
that CreateEventProc will automatically insert the complete procedure
declaration, including all of the correct parameters. CreateEventProc
returns the line number on which the procedure begins, so once you've called
CreateEventProc , add one to the result and use this with InsertLines to
insert the body of the event procedure. For example, the code below creates
a Workbook_Open procedure containing a Msgbox statement in the ThisWorkbook
module of the Active Workbook.

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With



--
Don Guillett
SalesAid Software

"Nigel Stevens" wrote in message
...
Could anybody help me

I wish to include a Worksheet_Change event on a
spreadsheet, so that I can record when the value in a cell
is changed.

I can do this physically by: right clicking on the
worksheet tab, selecting the View code option and then
adding the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("M:M")) Is Nothing Then
Target.Offset(, 1).Value = Date
End If
End Sub

My query is how do I automate the setting up of this event
from a VB macro that creates the Excel worksheet? I wish
to do this to ensure certain functionality and formatting
is included in the worksheet when it is created.

Many thanks for any Help you can give me.

Nigel