Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create muliple event procedures via VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create muliple event procedures via VBA
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 "Derek Brussels" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create muliple event procedures via VBA
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create muliple event procedures via VBA
Either of these work (XL97):
Sub Sub1() Dim iSheet% For iSheet = 1 To ActiveWorkbook.Sheets.Count ActiveWorkbook.VBProject.VBComponents(iSheet).Code Module.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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create muliple event procedures via VBA
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create muliple event procedures via VBA
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create muliple event procedures via VBA
Hi,
My problem is when I add With DataBk.VBProject.VBComponents(Sht.CodeName).CodeMo dule .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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Event procedures: who is calling? | Excel Programming | |||
Event procedures for spreadsheet cells ? | Excel Programming | |||
many similar event procedures | Excel Programming | |||
Event Procedures in an Add-In | Excel Programming | |||
learning event procedures | Excel Programming |