I am trying to insert via code, code into ThisWorkbook. I have read
Chip Pearson site and seen some examples by Bob Phillips which I am
trying to replicate without much look. I returns a syntax error. I am
aware of the Trusted VBA and Reference to Microsoft VBA Extensibility.
The code I am running is as follows. Could anyone assist?




Sub Populate_TW()

Dim StartLine As Long

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Application.ScreenUpdating = False"& vbCrLf & _
"Dim sh As Worksheet"& vbCrLf & _
"For Each sh In ThisWorkbook.Worksheets"& vbCrLf & _
"sh.Select"& vbCrLf & _
"Application.GoTo Reference:=sh.Range("a1"), Scroll:=True"& vbCrLf &
_
"Next sh"& vbCrLf & _
"ThisWorkbook.Sheets("Input").Select"& vbCrLf & _
"Application.ScreenUpdating = True"& vbCrLf & _
"Dim myArray As Variant"& vbCrLf & _
"Dim arName As String"& vbCrLf & _
"arName = "Users""& vbCrLf & _
"myArray = ThisWorkbook.Names(arName).RefersToRange.Value"& vbCrLf & _
"With Application"& vbCrLf & _
"If IsError(.Match(.UserName, myArray, 0)) Then"& vbCrLf & _
"ThisWorkbook.Sheets("Blank Sheet").Select"& vbCrLf &_
"MsgBox "You are NOT Permitted to access this File " & vbNewLine & _"&
vbCrLf & _
"" " & vbNewLine & _"& vbCrLf & _
""Please Contact " & vbNewLine & _"& vbCrLf & _
"" " & vbNewLine & _"& vbCrLf & _
""Joe Bloggs at " & vbNewLine & _"& vbCrLf & _
"" " & vbNewLine & _"& vbCrLf & _
""ABCGroup +0019 69944000""& vbCrLf & _
"Application.DisplayAlerts = False"& vbCrLf & _
"ThisWorkbook.Close False"& vbCrLf & _
"Else"& vbCrLf & _
"End If"& vbCrLf & _
"End With"& vbCrLf & _
"ThisWorkbook.Sheets("Input").Select"
End Sub