Chip Pearson explains it all:
http://www.cpearson.com/excel/vbe.aspx

Make sure you read the instructions at the top (missing references and wrong
security settings will cause it to fail).

I stole this from Chip's site:

Option Explicit
Sub CreateEventProcedure()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim wks As Worksheet

Const DQUOTE = """" ' one " character

Set wks = Worksheets.Add
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(wks.CodeName)
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Activate", "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, " MsgBox " & DQUOTE & "Hello World" & DQUOTE
End With
End Sub

I added the wks stuff and changed the name of the procedure.

===========
Is there anyway you could create a template worksheet that already has this code
-- either in the receiving workbook or even on your harddrive.

It may be easier (er, less prone to failure) to insert a new sheet from that
template, then copy data from the "real" sending sheet.



CLR wrote:

Hi All............

I am making a small program in which I open a second Excel workbook and
extract a sheet from it into my Main workbook. I do not know the name of the
sheet, and assign it "UserSheetName" variable, and then I can populate and
manipulate data on that sheet at will.....and close te second
workbook......that part all works fine.

What I would like to do now, is from a macro in my Main workbook, I would
like to create a ChangeEvent macro into to that "UserSheetName" sheet that is
now located also in my Main workbook.

Is this a doable thing?

Vaya con Dios,
Chuck, CABGx3

--

Dave Peterson