Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Populate ThisWorkbook Q
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 454
Default Populate ThisWorkbook Q
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
..InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Populate ThisWorkbook Q
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" wrote in message
ups.com...
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 454
Default Populate ThisWorkbook Q
Well I spotted no double quotes around the sheets Splash; User; Info.
Now I'm just stuck seemingly on Msg1



Sean wrote:
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 454
Default Populate ThisWorkbook Q
Thanks Bob, I missed the last quote on the line - "arName =
""MyUsers""" & vbCr & _


Bob Phillips wrote:
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" wrote in message
ups.com...
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 454
Default Populate ThisWorkbook Q
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 wrote:
Thanks Bob, I missed the last quote on the line - "arName =
""MyUsers""" & vbCr & _


Bob Phillips wrote:
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" wrote in message
ups.com...
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 454
Default Populate ThisWorkbook Q
I guess the & in the line was the prob


Sean wrote:
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 wrote:
Thanks Bob, I missed the last quote on the line - "arName =
""MyUsers""" & vbCr & _


Bob Phillips wrote:
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" wrote in message
ups.com...
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default Populate ThisWorkbook Q
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" wrote in message
ups.com...
I guess the & in the line was the prob


Sean wrote:
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 wrote:
Thanks Bob, I missed the last quote on the line - "arName =
""MyUsers""" & vbCr & _


Bob Phillips wrote:
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" wrote in message
ups.com...
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub




Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 454
Default Populate ThisWorkbook Q
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!


Chip Pearson wrote:
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" wrote in message
ups.com...
I guess the & in the line was the prob


Sean wrote:
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 wrote:
Thanks Bob, I missed the last quote on the line - "arName =
""MyUsers""" & vbCr & _


Bob Phillips wrote:
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" wrote in message
ups.com...
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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate ThisWorkbook via Code Sean Excel Programming 13 January 14th 07 06:03 PM
Help with ThisWorkbook David Excel Programming 5 May 27th 05 11:47 PM
Help with ThisWorkbook David Excel Programming 0 May 27th 05 09:54 PM
Help with ThisWorkbook K Dales[_2_] Excel Programming 0 May 27th 05 09:50 PM


All times are GMT +1. The time now is 01:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement