Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2007

Tip: Looking for answers? Try searching our database.

Inserting Code within ThisWorkbook via Code Q

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sean - 22 Jan 2007 16:45 GMT
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("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("BeforeClose", "Workbook") + 1
.InsertLines StartLine, msg1 & vbCr & msg2
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub
Sean - 22 Jan 2007 17:00 GMT
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".

> 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
[quoted text clipped - 40 lines]
> LockWindowUpdate 0&
> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.