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

 

[RoryA]

Is there a reason you don't simply use the workbook level SheetActivate event instead?

 

[drewzilla652]

Please expound on that. The sheet is being created just prior to my need to add a Worksheet_Activate event for that sheet.
Is that the same event handler to which you are referring?

 

[RoryA]

No, the workbook_sheetactivate event fires for all worksheets in the workbook (no matter when or how they were added) so if you use the same (or roughly the same) code for each activate event, it's usually better to use that, since you can code it in advance.
It's rarely necessary to write code in code, and if you do need to, it is generally best to avoid doing it in the same workbook as the code that is running if at all possible.

 

[drewzilla652]

I don't believe that will work. The workbook for which the worksheet_activate event is being created is a template file where several worksheets are being created during macro execution. The Summary worksheet (where the event macro is necessary) is basically a summary of the other worksheets. When changes are made to any of the individual worksheets, clicking on the Summary worksheet would trigger it being updated with the new information from the individual worksheets.
does that help?

 

[RoryA]

Why not just include the summary sheet in the template file to start with?

 

[drewzilla652]

Without going into detail, trust me when I say that will not work. The template file is not always used for this purpose.

 

[RoryA]

If the template file is not used, how is your code applied (given that it refers to ThisWorkbook)?

 

[drewzilla652]

the code exists in a separate workbook, that is essentially the "tool" used to process the data. The macros I attached in this thread create the event macro in the template file. This macro refers back to a macro in the "tool" file.

Specifically, the macro created is:

Private Sub Worksheet_Activate()
Application.Run "'N:\Operations\Macro Tools\Rate Analysis and Procurement Data Processor - Electric 21.0 BETA.xlsm'!updatesummary"

End Sub

 

[RoryA]

That doesn't really explain the reference to ThisWorkbook (rather than Activeworkbook) but never mind.
The simplest thing is probably just to add the line:

Debug.Print Activeworkbook.VBProject.Name

before you try and use the code name.