Codename not available unless VBA editor open

[drewzilla652]

This issue is starting to drive me nuts.
I'm developing a macro-based workbook to be used by many people in our organization.
I would like part of the code to create a Worksheet_Activate event macro, and this is where the issue lies.
I'm using Excel 2007 and I have the "trust access to the VBA project module" box checked. The code is not protected.
When running the code below, the codename can only be determined when the VBA editor is open.
I've added a subroutine to open the Extensions library, as you can see.
The makeevent macro is in a normal module, as is the updatesummary macro that is referred to by the worksheet_activate macro being created.

What am I doing wrong? Thanks so much in advance!

Sub makeevent()
If ThisWorkbook.VBProject.Protection = vbext_pp_locked Then
MsgBox "locked"
End If
AddVBEReference
Dim Cname As String
While Cname = ""
Cname = Sheets("RateAnalysisSummary").CodeName
Wend

h = "N:\Operations\Macro Tools\"
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents(Cname).CodeModule
StartLine = .CreateEventProc("activate", "Worksheet") + 1
.InsertLines StartLine, _
"Application.Run ""'" & h & masterfile & "'!updatesummary"
End With

End Sub


Private Sub AddVBEReference()

Dim Reference As Object
Dim OLB As String
Dim Vmajor, Vminor

'Microsoft Visual Basic for Applications Extensibility 5.3
OLB = "{0002E157-0000-0000-C000-000000000046}"
Vmajor = 5
Vminor = 3

On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid OLB, Vmajor, Vminor
If Err <> 0 And Err <> 32813 Then
MsgBox "VBE Object library not loaded." & vbCrLf _
& Err.Description
End If

End Sub

 

[drewzilla652]

OK, thanks.
It actually works, but opens the editor in the process. I can add code to quickly close it again, but would prefer that it doesn't open in the first place.

 

[RoryA]

It doesn't do that for me.
FYI, you don't need the Extensibility reference.

 

[drewzilla652]

Are you saying that the code works for you when the editor is closed? I have to have it open in order for the .codename to be determined.

 

[RoryA]

Yes, as long as you add the debug statement.

 

[jkmblogs]

Wow, page is only 11 years old but thanks for your help @RoryA