codemodule wiped when using VBComponents

[Amedee Van Gasse]

I'm sorry about the cross-post, I don't know which group is best for this
question.

I have encountered a problem with an Excel/VBA application I'm developing.
One of the things I do is creating sheets on the fly using VBA, like this:

Wb.Sheets.Add After:=Worksheets(Worksheets.Count)

Then I add event procedures to this sheet, following an example on Chip
Pearson's site: http://www.cpearson.com/excel/vbe.htm - Creating An Event
Procedure.

Dim StartLine As Long
Dim Code As String
Code = "Msgbox ""Hello World"",vbOkOnly"
With ActiveWorkbook.VBProject.VBComponents(Sh.CodeName).CodeModule
StartLine = .CreateEventProc("Activate", "Worksheet") + 1
.InsertLines StartLine, Code
End With

This works just fine as long as I don't close the file.


When I save, close and re-open the file again, there are two possible
situations:
- either the project was digitally signed, then I get a message that the
file was infected by a virus and that macros are disabled. I have checked
all code extensively but found no virus at all.
- or the project was not signed. In that case I get the usual security
warning and question about enabling/disabling macros.
After that, the result is always the same: the code module that contained
the VBComponents code, is completely wiped!


McAfee is installed on this machine and I suspect it has something to do
with this problem.


Does anyone know a workaround?

--
Amedee Van Gasse

To top-post is human, to bottom-post and snip is sublime.

Dit bericht is geplaatst in een nieuwsgroep. Post je evt antwoord of
vervolgvraag graag in dezelfde thread in de nieuwsgroep a.u.b.

 

[Dave Peterson]

I put edited your code to look like this:

Option Explicit
Sub testme()

Dim WB As Workbook
Dim StartLine As Long
Dim Code As String
Dim sh As Worksheet

Set WB = ActiveWorkbook

WB.Sheets.Add After:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet

Code = "Msgbox ""Hello World"",vbOkOnly"
With WB.VBProject.VBComponents(sh.CodeName).CodeModule
StartLine = .CreateEventProc("Activate", "Worksheet") + 1
.InsertLines StartLine, Code
End With

End Sub

And when the VBE was open, it worked fine. But when the VBE was closed, it had
trouble determining the sh.codename--but that's not the problem you described.

If you put this code into a new workbook and start a new workbook, does it work
there?

Maybe something else is going wrong.

=======
An alternative that maybe easier.

Create a workbook with a single sheet in it that has all your objects and code
that you want. Then instead of creating a new worksheet and adding code (and
objects???) at run time, you just have to add the worksheet that's based on this
template.

Kind of:

Option Explicit
Sub testme2()

Dim WB As Workbook
Dim sh As Worksheet

Set WB = ActiveWorkbook
With WB.Sheets
Set sh = .Add(after:=.Item(.Count), _
Type:="C:\my documents\excel\book1.xls")
End With

End Sub

 

[Amedee Van Gasse]

I put edited your code to look like this:

Option Explicit
Sub testme()

Dim WB As Workbook
Dim StartLine As Long
Dim Code As String
Dim sh As Worksheet

Set WB = ActiveWorkbook

WB.Sheets.Add After:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet

Code = "Msgbox ""Hello World"",vbOkOnly"
With WB.VBProject.VBComponents(sh.CodeName).CodeModule
StartLine = .CreateEventProc("Activate", "Worksheet") + 1
.InsertLines StartLine, Code
End With

End Sub

And when the VBE was open, it worked fine. But when the VBE was
closed, it had trouble determining the sh.codename--but that's not the
problem you described.

If you put this code into a new workbook and start a new workbook,
does it work there?

Maybe something else is going wrong.

=======
An alternative that maybe easier.

Create a workbook with a single sheet in it that has all your objects
and code that you want. Then instead of creating a new worksheet and
adding code (and objects???) at run time, you just have to add the
worksheet that's based on this template.

Kind of:

Option Explicit
Sub testme2()

Dim WB As Workbook
Dim sh As Worksheet

Set WB = ActiveWorkbook
With WB.Sheets
Set sh = .Add(after:=.Item(.Count), _
Type:="C:\my documents\excel\book1.xls")
End With

End Sub

[...]

Dave,

Thanks for the effort, but I think I will have to take a different
approach.
On our network, it is impossible to use the InsertLines method from
VBComponents. This is considered a possible hazard by our antivirus
(McAfee) so as a precaution it wipes the entire code module that contains
the InsertLines line.
Our network administrator has confirmed this because the monitoring
program saw a few "virus" alerts on my pc this week, all in the folder
where I was working on the workbook. He showed me the status page, and
more information on McAfee's site confirmed this: they block the
InsertLines operation because it is (according to McAfee) caused by a not
yet known macro virus.

I cannot use your second solution either because my Excel file will be
used on other machines by "low-tech" users, so relying on external files
is not really an option.

I have had a very interesting suggestion on
that I would like to share:

In ThisWorkbook:

dim withevetns xlapp as application

sub workbook_open()
set xlapp=application
end sub

And then:

Private Sub xlapp_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target
As PivotTable)

' My code comes here

End Sub

Which would be an event handler for *all* pivot tables on *all* sheets.
Which is exactly what I need.

--
Amedee Van Gasse

To top-post is human, to bottom-post and snip is sublime.

Dit bericht is geplaatst in een nieuwsgroep. Post je evt antwoord of
vervolgvraag graag in dezelfde thread in de nieuwsgroep a.u.b.

 

[Dave Peterson]

I'm not sure why you couldn't just put the template workbook in the same folder
as the "real" workbook--especially if you keep just one copy that all access.

But if you distribute the workbook and the users store it on their own, you
could tell them to store it with the "real" workbook. You could use that path
to get to the template.

But I hope your alternative approach works ok for you.

Good luck.

Amedee Van Gasse wrote: