Hello,

I want a VBA procedure to create a Worksheet_change procedure for each sheet
in a workbook.
I have tried many things, but each time Excel and VBA are shut down.

One Works fine:
ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile "C:\code.txt"

Muliple does NOT work:
For each sheet in activeworkbook.sheets
sheet.activate
ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile "C:\code.txt"
next

Anyone?

thank you,
Derek

Not sure I understand what you are asking in relation to the code you have
posted.

Worksheet_Change events are triggered when a worksheet content changes, your
code suggests adding module code.

It might be better to describe what you intend to do?

--

Regards,
Nigel
(E-Mail Removed)



"Derek Brussels" <(E-Mail Removed)> wrote in message
news:A12E13E5-8E0A-4FC7-AB83-(E-Mail Removed)...
> Hello,
>
> I want a VBA procedure to create a Worksheet_change procedure for each
> sheet
> in a workbook.
> I have tried many things, but each time Excel and VBA are shut down.
>
> One Works fine:
> ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile
> "C:\code.txt"
>
> Muliple does NOT work:
> For each sheet in activeworkbook.sheets
> sheet.activate
> ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile
> "C:\code.txt"
> next
>
> Anyone?
>
> thank you,
> Derek

Derek
Have you thought about using a Private Sub Workbook_SheetChange event
macro? That would be only one macro and it would fire whenever any cell in
the entire workbook changes content. The macro identifies the target sheet
as well as the target cell. Just a thought. HTH Otto
"Derek Brussels" <(E-Mail Removed)> wrote in message
news:A12E13E5-8E0A-4FC7-AB83-(E-Mail Removed)...
> Hello,
>
> I want a VBA procedure to create a Worksheet_change procedure for each
> sheet
> in a workbook.
> I have tried many things, but each time Excel and VBA are shut down.
>
> One Works fine:
> ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile
> "C:\code.txt"
>
> Muliple does NOT work:
> For each sheet in activeworkbook.sheets
> sheet.activate
> ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile
> "C:\code.txt"
> next
>
> Anyone?
>
> thank you,
> Derek

Either of these work (XL97):

Sub Sub1()
Dim iSheet%
For iSheet = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.VBProject.VBComponents(iSheet).CodeModule.AddFromFile
"C:\code.txt"
Next iSheet
End Sub ' Dave D-C

Sub Sub2()
Dim zSheet As Worksheet
For Each zSheet In ActiveWorkbook.Sheets

ActiveWorkbook.VBProject.VBComponents(zSheet.Name).CodeModule.AddFromFile
"C:\code.txt"
Next zSheet
End Sub



Derek Brussels <(E-Mail Removed)> wrote:
>I want a VBA procedure to create a Worksheet_change procedure for each sheet
>in a workbook.
>I have tried many things, but each time Excel and VBA are shut down.
>
>One Works fine:
>ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile "C:\code.txt"
>
>Muliple does NOT work:
>For each sheet in activeworkbook.sheets
>sheet.activate
>ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromFile "C:\code.txt"
>next
>
>Anyone?
>
>thank you,
>Derek

Thank you Dave for this answer.

However, the code you are suggesting does always result in "unrecoverable
error" and Excel shuts down. I have Excel 2003 and Windows XP. Tested on 2
computers.
It works fine to write code to ONE sheetcode module, but as soon as I want
to automatically to another sheet, the application shuts down.

So if anyone knows the answer, please help me out..

Thanks,
Derek

Well, rats.

Have you tried this on a brand new workbook?
I will rarely get a workbook into such a state
that I can hardly do anything with it without
getting an exception. Copy/pasting all the
sheets and modules and userforms into a new
workbook fixes the problem. Good luck. Dave

Derek Brussels <(E-Mail Removed)> wrote:
>Thank you Dave for this answer.
>
>However, the code you are suggesting does always result in "unrecoverable
>error" and Excel shuts down. I have Excel 2003 and Windows XP. Tested on 2
>computers.
>It works fine to write code to ONE sheetcode module, but as soon as I want
>to automatically to another sheet, the application shuts down.
>
>So if anyone knows the answer, please help me out..
>
>Thanks,
>Derek
>

Hi,

My problem is when I add

With DataBk.VBProject.VBComponents(Sht.CodeName).CodeModule
.AddFromFile "c:\work\code.txt"
End With

if the text is
Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
call querry
End Sub

then it works. However, if I add private in front, like
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
call querry
End Sub
Then Excel crashes.
Anyone has an idea?

Jingze





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