Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro resul Microsoft Excel for Windows has encountered a prob
I have 100s of excel workbooks and each of them has the same macros to run.
Sometimes it is required to change macros. I have written a macro in another excel page (using vbcomponent etc) and use it many times it and run successfully. Last time I have added a code to all excel workbooks starting "sub workbooks_deactivate()" and there was no problem. Whenever I deactivated a workbook, code in sub was running. But last time I tried the add another sub with same macro, EXCEL crashed. I found that if I delete the "sub workbooks_deactivate()" from excel workbooks, my code is running. Of course I don't want to update 100s of macros manually. Waiting for help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro resul Microsoft Excel for Windows has encountered a prob
You should have all your code in a template, and all your of sheets based on
that template. Look also into add-ins. -- * Please click Yes if this was helpful * Andy Smith Senior Systems Analyst Standard and Poor''s, NYC "KT1972" wrote: I have 100s of excel workbooks and each of them has the same macros to run. Sometimes it is required to change macros. I have written a macro in another excel page (using vbcomponent etc) and use it many times it and run successfully. Last time I have added a code to all excel workbooks starting "sub workbooks_deactivate()" and there was no problem. Whenever I deactivated a workbook, code in sub was running. But last time I tried the add another sub with same macro, EXCEL crashed. I found that if I delete the "sub workbooks_deactivate()" from excel workbooks, my code is running. Of course I don't want to update 100s of macros manually. Waiting for help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro resul Microsoft Excel for Windows has encountered a
I am not sure a template changes the problem once the workbooks are created.
Probably better to have the code in a single addin referenced/loaded by all users so the code only has to be changed in a single location. -- Regards, Tom Ogilvy "Andy Smith" wrote: You should have all your code in a template, and all your of sheets based on that template. Look also into add-ins. -- * Please click Yes if this was helpful * Andy Smith Senior Systems Analyst Standard and Poor''s, NYC "KT1972" wrote: I have 100s of excel workbooks and each of them has the same macros to run. Sometimes it is required to change macros. I have written a macro in another excel page (using vbcomponent etc) and use it many times it and run successfully. Last time I have added a code to all excel workbooks starting "sub workbooks_deactivate()" and there was no problem. Whenever I deactivated a workbook, code in sub was running. But last time I tried the add another sub with same macro, EXCEL crashed. I found that if I delete the "sub workbooks_deactivate()" from excel workbooks, my code is running. Of course I don't want to update 100s of macros manually. Waiting for help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro resul Microsoft Excel for Windows has encountered a
Could you try the following case? Because I think add-in or template will not
solve my problem. Lets create an excel workbook name is dummy1.xls including the code: Private Sub Workbook_Deactivate() MsgBox "deactivate" End Sub Private Sub Workbook_Open() MsgBox "OLD message" End Sub And another workbook (dummy2.xls) to change dummy1 subs: Sub ChangeMacros() Dim vbc As VBComponent, i As Long, j As Long, strProcName As String, strTemp As String Dim strMacroList As String Dim File 'On Error Resume Next File = "c:\dummy1.xls" Workbooks.Open Filename:=File, UpdateLinks:=0, ReadOnly:=False strMacroList = "" For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Type = vbext_ct_Document Or vbc.Type = vbext_ct_StdModule Then With vbc.CodeModule If vbc.CodeModule = "ThisWorkbook" Then i = .CountOfLines If i < 0 Then .DeleteLines 1, i End If .InsertLines 100, _ "Private Sub Workbook_Open()" & Chr(13) & _ "msgbox ""NEW message""" & Chr(13) & _ "End Sub" & Chr(13) End If End With End If Next ActiveWorkbook.Close True End Sub When I run ChangeMacros, excel is crashing. If I manually remove one of the subs (open or deactivate) from dummy1 and try to run ChangeMacros" sub in dummy2, it is running without any problem. Thanks "Tom Ogilvy" wrote: I am not sure a template changes the problem once the workbooks are created. Probably better to have the code in a single addin referenced/loaded by all users so the code only has to be changed in a single location. -- Regards, Tom Ogilvy "Andy Smith" wrote: You should have all your code in a template, and all your of sheets based on that template. Look also into add-ins. -- * Please click Yes if this was helpful * Andy Smith Senior Systems Analyst Standard and Poor''s, NYC "KT1972" wrote: I have 100s of excel workbooks and each of them has the same macros to run. Sometimes it is required to change macros. I have written a macro in another excel page (using vbcomponent etc) and use it many times it and run successfully. Last time I have added a code to all excel workbooks starting "sub workbooks_deactivate()" and there was no problem. Whenever I deactivated a workbook, code in sub was running. But last time I tried the add another sub with same macro, EXCEL crashed. I found that if I delete the "sub workbooks_deactivate()" from excel workbooks, my code is running. Of course I don't want to update 100s of macros manually. Waiting for help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Excel has encountered a problem | Excel Discussion (Misc queries) | |||
Microsoft Excel has encountered a problem | New Users to Excel | |||
Microsoft Excel has encountered a problem... | Excel Discussion (Misc queries) | |||
Microsoft Excel for Windows has encountered a problem.... | Excel Programming | |||
Microsoft Excel has encountered a problem and needs to close. HELP! | Excel Discussion (Misc queries) |