Hi Tom, i have had a look at Chip Pearsons site and have tried to make
my own code but with no luck. Do you think you could give me some
pointers and help ?


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

If you want to add an event to an existing workbook using code then Chip's
site gives a specific example. Beyond that, what type of help are you
looking for? I also suggested you just keep a record of the before and
then compare it to the after to highlight the changes. This seems simple
and something you should be capable of doing.

--
Regards,
Tom Ogilvy


"Les Stout" wrote in message
...
Hi Tom, i have had a look at Chip Pearsons site and have tried to make
my own code but with no luck. Do you think you could give me some
pointers and help ?


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

The code that i have tried is as below from his web page, it works great
but i need to add my code to the ActiveWorksheet of the activeWorkbook
and i am not sure how to do this.

Sub InsertProc()

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


Thanks Tom, i know this probably simple programming but it boggles my
mind !!

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

This code is what i need to put into the ActiveSheet

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = ActiveSheet.Columns("H:H")
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End Sub
-------------------------------------------------------------
I tried the code below and get an error at THE StartLine "EVENT HANDLER
IS INVALID"

Sub InsertProc1()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Worksheet", "Change") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Sub InsertProc()

Dim StartLine As Long
sName = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(sName).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub

Makes it the Change event of the the ActiveWorksheet.

--
Regards,
Tom Ogilvy


"Les Stout" wrote in message
...
The code that i have tried is as below from his web page, it works great
but i need to add my code to the ActiveWorksheet of the activeWorkbook
and i am not sure how to do this.

Sub InsertProc()

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


Thanks Tom, i know this probably simple programming but it boggles my
mind !!

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Tom, really appreciate your help.

regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom, sorry i am back, i changed the code to the following and then
excel closes due to an encounted error ?!! It puts in the the following
with no errors.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


But then bombs out !!

My code is as follows.

Sub InsertProc()

Dim StartLine As Long
sname = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(sname).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet")
.InsertLines 1, "Dim VRange As Range"
.InsertLines 2, "VRange = ActiveSheet.Columns(""H:H"")"
.InsertLines 3, "Target.Interior.ColorIndex = 37"
.InsertLines 4, "Target.Interior.Pattern = xlSolid"
End With
End Sub



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***