How to use VBA to copy this code to "thisworkbook" objects of all opened workbooks ?

[Amolin]

How to use VBA to copy this code from one opened workbook to all opened
workbooks ? I need this code under "Thisworkbook" object of each opened
workbook. Thank you!


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Application.SendKeys "{F9}"
End Sub

 

[Guest]

This seems to work:

Sub InsertCode()
Dim wb As Workbook
Dim code As String, checkline As String
Dim Ln As Long

checkline = "Workbook_SheetSelectionChange"
code = "Private Sub Workbook_SheetSelectionChange" & _
"(ByVal Sh As Object, ByVal Target As Range)" & vbCr & _
"Application.SendKeys ""{F9}""" & vbCr & _
"End Sub"
On Error Resume Next 'In case project is protected
For Each wb In Workbooks
With wb.VBProject.VBComponents("ThisWorkbook").CodeModule
Ln = .CountOfLines
'Don't add code if already exists
If Not .Find(checkline, 1, 1, Ln, 1) Then _
..InsertLines Ln + 1, code
End With
Next
On Error GoTo 0
End Sub

Regards,
Greg

 

[Amolin]

Thank you for your code!

But there is a error says: "Application define error or Object define
error"

"With wb.VBProject.VBComponents("ThisWorkbook").CodeModule"


could you give a help?

 

[Amedee Van Gasse]

Greg Wilson shared this with us in microsoft.public.excel.programming:

This seems to work:

*snip* code containing VBComponents.CodeModule.InsertLines

And if you have a McAfee virusscanner, it will cheerfully erase any
code module that contains .InsertLines.

 

[Amolin]

Thank you, I have Symantec Anti _Virus installed

 

[Amedee Van Gasse]

Amolin shared this with us in microsoft.public.excel.programming:

Thank you, I have Symantec Anti _Virus installed.

Which is no guarantee. I experienced the problem with McAfee, but other
AV may do the same. Perhaps not now but who knows about future versions?

The problem is with .InsertLines (and also with .DeleteLines). Some
macro viruses use this to add their own code to other workbooks. So a
proactive AV may not know the virus yet, but it sees the .InsertLines
activity and it say, better safe than sorry.

If you have a standalone AV, you can perhaps disable this particular
scanning feature. With a managed or network scanner: don't know,
perhaps not.

In My Humble Opinion it is important that not only you know there can
be a problem, but also WHY there can be a problem. That way you can
work around it.