Inserting Code within ThisWorkbook via Code Q
 

I have the following Code that that works great under Office XP/2003
versions except one problem PC that uses Office XP and Windows 2000. I
have a Reference set to Microsoft VBA Extensibility v 5.3. Is there an
issue with running code via VBA to insert Code into ThisWorkbook under
Win2000?

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

Sub Populate_TW_2()

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 = "Worksheets(""View"").Visible = True" & vbCr & _
"Dim sh As Worksheet" & vbCr & _
"For Each ws In Worksheets " & vbCr & _
"If ws.Name = ""View"" Then"

msg2 = "ws.Visible = True" & vbCr & _
"Else" & vbCr & _
"ws.Visible = xlVeryHidden" & vbCr & _
"End If" & vbCr & _
"Next"


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

Inserting Code within ThisWorkbook via Code Q
 

Worked it out as per http://www.cpearson.com/excel/vbe.htm
I hadn't changed the "Trusted Sources" tab, and put a check next to the
"Trust access to Visual Basic Project".



Sean wrote:
I have the following Code that that works great under Office XP/2003
versions except one problem PC that uses Office XP and Windows 2000. I
have a Reference set to Microsoft VBA Extensibility v 5.3. Is there an
issue with running code via VBA to insert Code into ThisWorkbook under
Win2000?

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

Sub Populate_TW_2()

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 = "Worksheets(""View"").Visible = True" & vbCr & _
"Dim sh As Worksheet" & vbCr & _
"For Each ws In Worksheets " & vbCr & _
"If ws.Name = ""View"" Then"

msg2 = "ws.Visible = True" & vbCr & _
"Else" & vbCr & _
"ws.Visible = xlVeryHidden" & vbCr & _
"End If" & vbCr & _
"Next"


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