Populate ThisWorkbook Q

[Sean]

I have received the following code from this WG, which places code
within ThisWorkbook, problem is I'm receiving a syntax error on both
message 1 & 2 and I'm just about lost at this stage of where I'm gone
wrong, any help appreciated

Sub Populate_TW()

Dim StartLine As Long
Dim msg1 As String, msg2 As String

Dim VBEHwnd As Long
On Error GoTo ErrH:
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk", _
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If

msg1 = "Dim myArray As Variant" & vbCr & _
"Dim arName As String" & vbCr & _
"Dim ws As Worksheet" & vbCr & _
"arName = ""MyUsers"" & vbCr & _
"myArray = ThisWorkbook.Names(arName).RefersToRange.Value"

msg2 = "With Application"& vbCr & _
"If IsError(.WorksheetFunction.Match(.UserName, myArray, 0)) Then " &
vbCr & _
"MsgBox ""You are NOT Permitted to access this File "" & vbCr & _" &
vbCr & _
""""" & vbCr & _" & vbCr & _
"""Please Contact Joe Bloggs at "" & vbCr & _" & vbCr & _
""""" & vbCr & _" & vbCr & _
"""ABC Group +9842 45 852462000""" & vbCr & _
"Application.DisplayAlerts = False" & vbCr & _
"ThisWorkbook.Close False" & vbCr & _
"Else" & vbCr & _
"For Each ws In Worksheets" & vbCr & _
"ws.Visible = True" & vbCr & _
"Next" & vbCr & _
"Worksheets("Splash").Visible = False" & vbCr & _
"Worksheets("Users").Visible = False" & vbCr & _
"Worksheets("info").Activate"& vbCr & _
"Application.DisplayAlerts = True" & vbCr & _
"End If"& vbCr & _
"End With"

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
..InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

 

[Bob Phillips]

This seems to compile okay

msg1 = "Dim myArray As Variant" & vbCr & _
"Dim arName As String" & vbCr & _
"Dim ws As Worksheet" & vbCr & _
"arName = ""MyUsers""" & vbCr & _
"myArray = & ThisWorkbook.Names(arName).RefersToRange.Value"

msg2 = "With Application" & vbCr & _
"If IsError(.WorksheetFunction.Match(.UserName, myArray, 0)) Then " & vbCr &
_
"MsgBox ""You are NOT Permitted to access this File "" & vbCr &" _
& vbCr & _
""""" & vbCr & _" & vbCr & _
"""Please Contact Joe Bloggs at "" & vbCr & _" & vbCr & _
""""" & vbCr & _" & vbCr & _
"""ABC Group +9842 45 852462000""" & vbCr & _
"Application.DisplayAlerts = False" & vbCr & _
"ThisWorkbook.Close False" & vbCr & _
"Else" & vbCr & _
"For Each ws In Worksheets" & vbCr & _
"ws.Visible = True" & vbCr & _
"Next" & vbCr & _
"Worksheets(""Splash"").Visible = False" & vbCr & _
"Worksheets(""Users"").Visible = False" & vbCr & _
"Worksheets(""info"").Activate" & vbCr & _
"Application.DisplayAlerts = True" & vbCr & _
"End If" & vbCr & _
"End With"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sean]

Well I spotted no double quotes around the sheets Splash; User; Info.
Now I'm just stuck seemingly on Msg1

 

[Sean]

Thanks Bob, I missed the last quote on the line - "arName =
""MyUsers""" & vbCr & _

 

[Sean]

New Problem!

My code runs and it populates ThisWorkbook, however when I open the new
file (file #2) it hits debug at this line

myArray = & ThisWorkbook.Names(arName).RefersToRange.Value

I have a Range Name called MyUsers, the sheet that MyUsers are in is
protected and hidden, not sure if that the issue

 

[Sean]

I guess the & in the line was the prob

New Problem!

My code runs and it populates ThisWorkbook, however when I open the new
file (file #2) it hits debug at this line

myArray = & ThisWorkbook.Names(arName).RefersToRange.Value

I have a Range Name called MyUsers, the sheet that MyUsers are in is
protected and hidden, not sure if that the issue

 

[Chip Pearson]

Sean,

As I pointed in a previous post, you will find it MUCH MUCH simpler if you
quit using line continuations and build the string up line-by-line in a
variable and then insert the final string. Line continuation have their
useful role to be sure, but creating monster strings in not one of those
roles.

Creating a large string line-by-line makes finding and fixing errors MUCH
simpler. Trust me on this one. E.g.,

Dim S As String
S = "First Line" & vbCrLf
S = S & "Second Line" & vbCrLf
S = S & "Third Line" & vbCrLf
' and so on

Using this method, the line that is causing the problem is VASTLY simpler to
find and fix than when you 10 line continuations in a single line of code.


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

 

[Sean]

Thanks Chip, advice taken, I just had most of the code done and didn't
wish to re-hash and get myself tied up in more knots!