Excel Add-In duplicates code execution

[bajo71]

Hello,

The following code resides in an Add-In on Excel 2007/Windows XP. I simply want to import .bas modules in the current workbook, via custom menu event driven functionality(included below the ImportModules() subroutine). If I step through this code, it works fine, but triggered from custom menu/ribbon function in add-in, it duplicates the import of modules. So, without the FOR-NEXT statement below I would have 8 modules instead of 4. I'm thinking it has something to do with one of the events.

Many thanks for your help.

Sub ImportModules()
On Error GoTo err_ModImport
Dim vbCom As Object

ActiveWorkbook.VBProject.VBComponents.Import "C:\TransposeVBA\Globals.bas"
ActiveWorkbook.VBProject.VBComponents.Import "C:\TransposeVBA\MainGasGauge.bas"
ActiveWorkbook.VBProject.VBComponents.Import "C:\TransposeVBA\modCheckSum.bas"
ActiveWorkbook.VBProject.VBComponents.Import "C:\TransposeVBA\modFunctions.bas"
ActiveWorkbook.VBProject.VBComponents.Import "C:\TransposeVBA\UserForm2.frm"

Set vbCom = ActiveWorkbook.VBProject.VBComponents
'Without the following For statement, I get duplicated modules
For Each objMod In vbCom
If objMod.Type = 1 And Right(objMod.Name, 1) = "1" Then
vbCom.Remove vbcomponent:=vbCom.Item(objMod.Name)
End If
Next

err_ModImport:
If Err.Number = 60061 Then
Resume Next
ElseIf Err.Number = 0 Then
Exit Sub
Else: MsgBox ("Error# " & Err.Number & " " & Err.Description)
End If

End Sub

<<Here's the code for Workbook events...
Private Sub Workbook_Open()

Set myMenuBar = Application.CommandBars(1)
iHelpMenu = myMenuBar.Controls("Help").Index
Set newMenu = myMenuBar.Controls.Add(msoControlPopup, Before:=iHelpMenu)
newMenu.Caption = ("E&BS")

With newMenu.Controls.Add(Type:=msoControlButton, ID:=1)
.Caption = "Import Modules"
.OnAction = "Transpose.xla!ImportModules()"
.Enabled = True
.Style = msoButtonCaption
End With



End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim MenuDel As Boolean

MenuDel = False
Application.CommandBars(1).Controls("E&BS").Delete
Do Until MenuDel = True
If Application.CommandBars(1).Controls("E&BS") Is Nothing Then
MenuDel = True
Else: Application.CommandBars(1).Controls("E&BS").Delete
End If
Loop

End Sub

Private Sub Workbook_Activate()
On Error GoTo err_Activate
Set zCmdBar = Application.CommandBars(1).Controls.Item("E&BS")
With zCmdBar.Controls
.Item("Import Modules").Enabled = True
End With
err_Activate:
Exit Sub

End Sub

Private Sub Workbook_Deactivate()
On Error GoTo err_Deactivate
Set zCmdBar = Application.CommandBars(1).Controls.Item("E&BS")
With zCmdBar.Controls
.Item("Import Modules").Enabled = False
End With
err_Deactivate:
Exit Sub

End Sub

 

[bajo71]

I will add this much; the form UserForm2.frm never gets duplicated, only the modules.