drewzilla652
New Member
- Joined
- Oct 19, 2010
- Messages
- 19
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
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