Insert by macro a workbook open event inside a workbook just created

Question

Insert by macro a workbook open event inside a workbook just created

Anonymous

I've tried to insert the following code in a routine to which I've passed the reference to the worksheet belonging to a workbook just created and saved, but it does not add any code to the new workbook, without issuing any errors. Any idea? Thanks in advance for your answers.

Dim ExcelWorkbook As Workbook

Dim ExcelProject As VBProject

Dim ThisWorkBookPrj As VBComponent

Dim WBname As String, WBtest As String

Dim VBAcode As String

Dim indWB As Integer

Set ExcelWorkbook = ExcelWorksheet.Parent

WBtest = ExcelWorkbook.Name

For indWB = 1 To Application.VBE.VBProjects.Count

WBname = Right(Application.VBE.VBProjects.Item(indWB).Filename, InStr(StrReverse(Application.VBE.VBProjects.Item(indWB).Filename), "") - 1)

If WBname = WBtest Then Exit For

Next indWB

If indWB <= Application.VBE.VBProjects.Count Then

Set ExcelProject = Application.VBE.VBProjects.Item(indWB)

Set ThisWorkBookPrj = ExcelProject.VBComponents.Item("ThisWorkBook")

VBAcode = "Private Sub Workbook_Open()" & vbLf + "with sheets(""" & ExcelWorksheet.Name & """)" & vbLf & _

".Protect Password:=""" & password & """, UserInterfaceOnly:=True" & vbLf & _

".EnableOutlining = True" & vbLf & "End With" & vbLf & "End Sub"

ThisWorkBookPrj.CodeModule.AddFromString VBAcode

End If

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

To:

Re: add code to "workbook just created"

Try saving the new workbook before running your code.

'---

In my opinion, it is not worth the effort as errors can abound.

Create a workbook, containing the code, and save it as a template to be used as needed.

'---

The free Excel workbook "Professional_Compare" is now available at MediaFire.(compares every cell Or every row in two worksheets - choice of compare type & very fast)Download (no ads) from: *http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents*

0 comments

2 additional answers

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 2

To:

Re: add code to "workbook just created"

Try saving the new workbook before running your code.

'---

In my opinion, it is not worth the effort as errors can abound.

Create a workbook, containing the code, and save it as a template to be used as needed.

'---

The free Excel workbook "Professional_Compare" is now available at MediaFire.(compares every cell Or every row in two worksheets - choice of compare type & very fast)Download (no ads) from: *http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents*

Thanks awfully!