Create muliple event procedures via VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Derek Brussels - 28 Jan 2008 15:04 GMT

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

Reply to this Message

Nigel - 28 Jan 2008 16:38 GMT

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?

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

> Hello,
>
[quoted text clipped - 18 lines]
> thank you,
> Derek

Reply to this Message

Otto Moehrbach - 28 Jan 2008 16:50 GMT

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

> Hello,
>
[quoted text clipped - 18 lines]
> thank you,
> Derek

Reply to this Message

Dave D-C - 28 Jan 2008 17:05 GMT

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

>I want a VBA procedure to create a Worksheet_change procedure for each sheet
>in a workbook.
[quoted text clipped - 13 lines]
>thank you,
>Derek

Reply to this Message

Derek Brussels - 28 Jan 2008 19:51 GMT

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

Reply to this Message

Dave D-C - 28 Jan 2008 21:12 GMT

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

>Thank you Dave for this answer.
>
[quoted text clipped - 8 lines]
>Thanks,
>Derek

Reply to this Message

jingze - 24 Oct 2008 01:39 GMT

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

Reply to this Message