Chip, I have no idea. But I did get it working, the way it is supposed
to, thanks for your guidance


Chip Pearson wrote:

Sean,

The Private Declare statements must not be within a Sub or Function
procedure. They must appear outside of and above any procedure in the
module, right after "Option Explicit" (and you are using "Option Explicit"
aren't you?).


--
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...
Chip I tried your other code but get a compile error "only comment may
appear after End sub etc". I placed it like the following

Sub Populate_TW()

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal ClassName As String, ByVal WindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "user32" _
(ByVal hWndLock As Long) As Long


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

' My Code in Here

Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub





Chip Pearson wrote:

Sean,

Take out the line

Application.VBE.MainWindow.Visible = True

I have no idea what I was thinking when I put line of code on the web
site.
I'll look back over the page to see if that line serves any purpose. I
think
it is a mistake.


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


"Sean" wrote in message
oups.com...
Chip, I placed the following code from your site but the Editor still
remains open once the code is completed. Not too worried about the
flicker

Sub Populate_TW()
Application.VBE.MainWindow.Visible = False
Dim StartLine As Long
Dim msg1 As String, msg2 As String

'
' My Code is in here
'
Application.VBE.MainWindow.Visible = True
End Sub




Chip Pearson wrote:

You can't completely get around that, but you get pretty close with
the
"Eliminating Screen Flickering" section near the bottom of
http://www.cpearson.com/excel/vbe.htm


--
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...
Guys I'm delighted with the result. I runthe following code as part
of
another module and it works as I like, apart for one issue. When I
run
it, the Microsoft Visual Basic project window remains open, why is
that
and how can I close it?

Sub Populate_TW()
Dim StartLine As Long
Dim msg1 As String, msg2 As String


msg1 = "Dim sh As Worksheet" & vbCr & _
"With Application" & vbCr & _
"If .UserName = ""John"" Or .UserName = ""Joe"" Or .UserName =
""Johnny"" Then"


msg2 = "ThisWorkbook.Sheets(""E-Mail"").Select" & vbCr & _
"Else" & vbCr & _
"ThisWorkbook.Sheets(""E-Blank"").Select" & 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 +0019 69944000""" & vbCr & _
"ThisWorkbook.Sheets(""E-Mail"").Select" & vbCr & _
"Application.DisplayAlerts = False" & vbCr & _
"ThisWorkbook.Close False" & 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
End Sub







Sean wrote:

Thanks Guys. I wasn't aware that there was a limit on the
continuation
lines, how many is it?


Greg Wilson wrote:

I took some liberty with your code and made a few changes. The
main
problems
were that quoted text withing quotes must have double quotes.
Also,
you
had
too many line continuations which raised an error. I split it up
into
two
strings to avoid this. Very little testing.

Sub Populate_TW()
Dim StartLine As Long
Dim msg1 As String, msg2 As String

msg1 = "Dim sh As Worksheet" & vbCr & _
"Dim myArray As Variant" & vbCr & _
"Application.ScreenUpdating = False" & vbCr & _
"For Each sh In ThisWorkbook.Worksheets" & vbCr & _
"sh.Select" & vbCr & _
"Application.GoTo Reference:=sh.Range(""a1""), Scroll:=True" &
vbCr
& _
"Next sh" & vbCr & _
"ThisWorkbook.Sheets(""Input"").Select" & vbCr & _
"Application.ScreenUpdating = True" & vbCr & _
"myArray = Range(""Users"").Value"

msg2 = "With Application" & vbCr & _
"If IsError(.Match(.UserName, myArray, 0)) Then" & vbCr & _
"ThisWorkbook.Sheets(""Blank Sheet"").Select" & vbCr & _
"MsgBox ""You are NOT Permitted to access this File "" & vbCr &
_" &
vbCr & _
"""Please Contact Joe Bloggs at "" & vbCr & _" & vbCr & _
"""ABCGroup +0019 69944000""" & vbCr & _
"ThisWorkbook.Sheets(""Input"").Select" & vbCr & _
"Application.DisplayAlerts = False" & vbCr & _
"ThisWorkbook.Close False" & 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
End Sub

Regards,
Greg