PDA

View Full Version : Solved: Workbook_Open entry



R1C1
04-03-2006, 10:44 AM
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. :banghead:

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

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

Thanks

R1C1

Bob Phillips
04-03-2006, 10:52 AM
'----------------------------------------------------------------
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

R1C1
04-03-2006, 11:04 AM
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
04-03-2006, 11:28 AM
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
04-03-2006, 11:49 AM
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.

:clap2: Thanks to both of you, :clap2:

R1C1

mdmackillop
04-03-2006, 11:56 AM
Happy to help out. I'll mark this solved.

R1C1
04-03-2006, 12:13 PM
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
04-03-2006, 12:16 PM
No Problem. That's where it should be, but it's temporarily out of action.