Workbook_Open event Created via VBA doesnt persist after closing and reopening file

[AswathiS]

I am creating an .xls file via VBA and making it protected
I need to add workbook_Open event for this workbook.
I have added it using the below sample code

Sub CreateEventProcedure()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character


        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("ThisWorkbook")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CreateEventProc("Open", "Workbook")
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
        End With
    End Sub

Throiugh code , i created the file, protected it , added the above code and saved.
But when i closed and re-open, this workbook_open event got vanished.
Please help

 

[RoryA]

Welcome to the forum.

Are you sure that:
1. The correct workbook was active when you ran that code?
2. You saved it in the correct format?

 

[AswathiS]

@RoryA Thanks for your Reply
For answering your questions
1. Yes the Proper workbook was active when i ran this code
2.I saved the workbook as follows

With wNewBook
.Sheets(1).Activate
.SaveLinkValues = False
.Save
.Close
End With

 

[RoryA]

You must have done a SaveAs at some point if it's a new workbook - what format did you specify?

 

[AswathiS]

Yes
I have done a save as when ever i create the workbook
Set wNewBook = Workbooks.Add
With wNewBook
.Title = Abc
.Subject =cde
.Application.SheetsInNewWorkbook = pqr
.SaveAs gt_AllFiles.sApprovalFile, FileFormat:=51
.Close
End With

 

[AswathiS]

@RoryA

Yes
I have done a save as when ever i create the workbook
Set wNewBook = Workbooks.Add
With wNewBook
.Title = Abc
.Subject =cde
.Application.SheetsInNewWorkbook = pqr
.SaveAs gt_AllFiles.sApprovalFile, FileFormat:=51
.Close
End With

 

[RoryA]

Fileformat 51 is xlOpenXMLWorkbook, which is not a macro-enabled format.

 

[AswathiS]

Can you please provide me the proper format I can use here

 

[RoryA]

What format are you trying to save it as? Your question mentioned .xls but it looks like you perhaps wanted .xlsm? If so, use 52 rather than 51.

 

[AswathiS]

I know .xlsm is a macro enabled workbook.
So If I want to add workbook_open event in this file, should I save it as .xlsm
.xls can't add a workbook_open event?
Sorry if I am asking something wrong.