Sean,

You've got too many line continuations and you haven't properly used quotes.
Rather than trying to build the entire event procedure code with so many
line continuations, you'll find it MUCH easier to create and maintains the
code if you build it up line by line. E.g.,

Dim S As String
S = "first line" & vbCrLf
S = S & "next line" & vbCrLf
S = S & "another line" & vbCrLf
' and so on

This will make life MUCH simpler.

Also, you're not using quotes properly. To include a quote mark within the
string, you must use two quote characters. E.g.,

Dim S As String
S = "This ""word"" is quoted."
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Sean" wrote in message
ups.com...
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