I am dispersing large amounts of monthly data by creating and emailing
workbooks in a macro. In the largest of these workbooks I would like to
create a macro that would be triggered by clicking the mouse in various cells
of one sheet. The macro itself is no problem, it's programmatically getting
the macro and the event code into the new workbook that is the problem. So
far I have not found a way to do this. Does anyone have any suggestions?

Here is some code that creates some Change event code

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbOYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

It creates this code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
ans = MsgBox("All OK", vbOYesNo)
If ans = vbNo Then Cancel = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Warren" wrote in message
...
I am dispersing large amounts of monthly data by creating and emailing
workbooks in a macro. In the largest of these workbooks I would like to
create a macro that would be triggered by clicking the mouse in various
cells
of one sheet. The macro itself is no problem, it's programmatically
getting
the macro and the event code into the new workbook that is the problem.
So
far I have not found a way to do this. Does anyone have any suggestions?

Bob: Thanks for the help. Although, it didn't work. After the first crash,
I single stepped through the code and it works until it comes to the
..InsertLines line. I got a runtime error "The object invoked has
disconnected from its client." The only thing I could find at Microsoft was
an article that said all globals are reset when using .CreateEventProc. I
guess that would reset StartLine, but I don't know. If you could give me some
more ideas I would appreciate it. By the way, could you recommend a good
reference source for some of the more obscure commands in VBA.

"Bob Phillips" wrote:

Here is some code that creates some Change event code

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbOYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

It creates this code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
ans = MsgBox("All OK", vbOYesNo)
If ans = vbNo Then Cancel = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Warren" wrote in message
...
I am dispersing large amounts of monthly data by creating and emailing
workbooks in a macro. In the largest of these workbooks I would like to
create a macro that would be triggered by clicking the mouse in various
cells
of one sheet. The macro itself is no problem, it's programmatically
getting
the macro and the event code into the new workbook that is the problem.
So
far I have not found a way to do this. Does anyone have any suggestions?