Create muliple event procedures via VBA

[Derek Brussels]

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

 

[Nigel]

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?

 

[Otto Moehrbach]

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

 

[Dave D-C]

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]

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

 

[Dave D-C]

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

 

[jingze]

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 ***