Hmmmm......that stuff is so far over my head, I don't have enough time left
to even think about beginning to learn it..........I believe your second
solution is more my style........so I'll look in to the "template" method

Thanks Dave,

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

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