Solved: Workbook_Open entry

[R1C1]

Is there a way for a Workbook_Open procedure to be entered into an Excel file with a VBA macro? The currently open workbook does not have a module installed. I need to set a "ThisWorkbook.Path" so the open file will always look within it's directory for another workbook. I have a common name workbook that is called when executing a macro on the currently open workbook. The Subs below works well when entered manually but I would like to automate this while performing other tasks, with a macro, on the open workbook.

Private Sub Workbook_Open()
Application.Caption = ThisWorkbook.Path
End Sub

Private Sub Workbook_Deactivate()
Application.Caption = " "
End Sub

Thanks

R1C1

[Bob Phillips]

[vba]
'----------------------------------------------------------------
Sub AddWorkbookEventProc()
'----------------------------------------------------------------
Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Application.Caption = ThisWorkbook.Path"
StartLine = .CreateEventProc("Deactivate", "Workbook") + 1
.InsertLines StartLine, _
"Application.Caption = """""
End With

End Sub
[/vba]

[R1C1]

Thank you very much for the quick reply.

I loaded the macro and ran it. I received the following error message on the "With ActiveWorkbook. ..." line:

Run-time error '1004':

Method 'VBProject' of object'_Workbook' failed

Any suggestions?

Thanks

R1C1

[mdmackillop]

You may need to set permission for the code to change your modules.
Try Tools/Macros/Security/Trusted Publishers Tick "Trust Access to VB Projects"
Regards
MD

[R1C1]

Thanks to a Master and a Guru for helping out this ol' newbie. The Master had the right VBA and the Guru made sure I was ticked off. LOL.

Thanks to both of you,

R1C1

[mdmackillop]

Happy to help out. I'll mark this solved.

[R1C1]

Sorry, the newbie is so new I couldn't figure out where the Solved was. I click Thread Tools and do not have that option.

Thanks again,

R1C1

[mdmackillop]

No Problem. That's where it should be, but it's temporarily out of action.