VBA macro resul Microsoft Excel for Windows has encountered a prob

[Guest]

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

 

[Guest]

You should have all your code in a template, and all your of sheets based on
that template. Look also into add-ins.

 

[Guest]

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.

 

[Guest]

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