I have an Excel 2003 template that includes 2 macros, the final bit of code
removes both macros, as shown below
--------------------------------------------------------------------------------
On Error Resume Next

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module3")

vbCom.Remove VBComponent:= _
vbCom.Item("Module4")

On Error Goto 0

End Sub
--------------------------------------------------------------------------------

Users often have multiple files created from the same template open at the
same time, unsaved. When the macro runs it sometimes removes the modules from
the wrong file rather than the active one.

What code do I need to add to ensure that the modules are deleted from the
active file only?

I'd try:

set vbCom = activeworkbook.VBProject.VBComponents



Code Numpty wrote:

I have an Excel 2003 template that includes 2 macros, the final bit of code
removes both macros, as shown below.
--------------------------------------------------------------------------------
On Error Resume Next

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module3")

vbCom.Remove VBComponent:= _
vbCom.Item("Module4")

On Error Goto 0

End Sub
--------------------------------------------------------------------------------

Users often have multiple files created from the same template open at the
same time, unsaved. When the macro runs it sometimes removes the modules from
the wrong file rather than the active one.

What code do I need to add to ensure that the modules are deleted from the
active file only?

--

Dave Peterson

So far so good.
Looks like ActiveWorkbook does the trick better than ActiveVBProject.

I just wish I knew why. Thanks Dave.

"Dave Peterson" wrote:

I'd try:

set vbCom = activeworkbook.VBProject.VBComponents

Because the active project in the VB editor is not necessarily the active
workbook in Excel. Think about it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Code Numpty" wrote in message
...
So far so good.
Looks like ActiveWorkbook does the trick better than ActiveVBProject.

I just wish I knew why. Thanks Dave.

"Dave Peterson" wrote:

I'd try:

set vbCom = activeworkbook.VBProject.VBComponents