software

I have set up many files using the Workbook_Open code when a file is
opened. I, in turn, need to delete the code after the file has
processed. I was able to do this, but now my computer won't accept.
The version of Microsoft I am having trouble with is quot;Microsoft Office
Basic Edition 2003quot; and the code is as follows:

'deletes workbook open code
Dim DeleteWBOpen As Object
Dim StartLine As Long
Dim HowManyLines As Long

Set DeleteWBOpen =
ThisWorkbook.VBProject.VBComponents(quot;ThisWorkbookquot; ).CodeModule
With DeleteWBOpen
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With

I have also tried the following and it still gets hung up.

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

I have checked the quot;Microsoft Visual Basics for Applications
Extensibilityquot; box.

Any help??

Thank you--
Paige
------------------------------------------------------------------------
Paige's Profile: www.excelforum.com/member.php...oamp;userid=23096
View this thread: www.excelforum.com/showthread...hreadid=505912Is the project protected? That would be a problem?

Did you allow access to your project:
In Excel (not the VBE)
tools|macro|macro security|trusted Publishers tab
check Trust access to Visual Basic Project

This is a setting that is a user-by-user setting (if I recall correctly). That
can represent a problem.

Paige wrote:
gt;
gt; I have set up many files using the Workbook_Open code when a file is
gt; opened. I, in turn, need to delete the code after the file has
gt; processed. I was able to do this, but now my computer won't accept.
gt; The version of Microsoft I am having trouble with is quot;Microsoft Office
gt; Basic Edition 2003quot; and the code is as follows:
gt;
gt; 'deletes workbook open code
gt; Dim DeleteWBOpen As Object
gt; Dim StartLine As Long
gt; Dim HowManyLines As Long
gt;
gt; Set DeleteWBOpen =
gt; ThisWorkbook.VBProject.VBComponents(quot;ThisWorkbookquot; ).CodeModule
gt; With DeleteWBOpen
gt; StartLine = 1
gt; HowManyLines = .CountOfLines
gt; DeleteLines StartLine, HowManyLines
gt; End With
gt;
gt; I have also tried the following and it still gets hung up.
gt;
gt; Sub DeleteAllVBA()
gt;
gt; Dim VBComp As VBIDE.VBComponent
gt; Dim VBComps As VBIDE.VBComponents
gt;
gt; Set VBComps = ActiveWorkbook.VBProject.VBComponents
gt;
gt; For Each VBComp In VBComps
gt; Select Case VBComp.Type
gt; Case vbext_ct_StdModule, vbext_ct_MSForm, _
gt; vbext_ct_ClassModule
gt; VBComps.Remove VBComp
gt; Case Else
gt; With VBComp.CodeModule
gt; DeleteLines 1, .CountOfLines
gt; End With
gt; End Select
gt; Next VBComp
gt;
gt; End Sub
gt;
gt; I have checked the quot;Microsoft Visual Basics for Applications
gt; Extensibilityquot; box.
gt;
gt; Any help??
gt;
gt; Thank you
gt;
gt; --
gt; Paige
gt; ------------------------------------------------------------------------
gt; Paige's Profile: www.excelforum.com/member.php...oamp;userid=23096
gt; View this thread: www.excelforum.com/showthread...hreadid=505912

--

Dave Peterson

Paige,

Working on the same workbook, I've found that you may need to just comment
out the lines

Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ThisWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

With myVBA.CodeModule
For j = 1 To .CountOfLines
temp = quot;'quot; amp; .Lines(j, 1)
.DeleteLines j
.InsertLines j, temp
Next j
End With

This should work for the activeworkbook, if the code is in another book:

Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

With myVBA.CodeModule
.DeleteLines 1, .CountOfLines
End With

HTH,
Bernie
MS Excel MVPquot;Paigequot; gt; wrote in message
...
gt;
gt; I have set up many files using the Workbook_Open code when a file is
gt; opened. I, in turn, need to delete the code after the file has
gt; processed. I was able to do this, but now my computer won't accept.
gt; The version of Microsoft I am having trouble with is quot;Microsoft Office
gt; Basic Edition 2003quot; and the code is as follows:
gt;
gt; 'deletes workbook open code
gt; Dim DeleteWBOpen As Object
gt; Dim StartLine As Long
gt; Dim HowManyLines As Long
gt;
gt; Set DeleteWBOpen =
gt; ThisWorkbook.VBProject.VBComponents(quot;ThisWorkbookquot; ).CodeModule
gt; With DeleteWBOpen
gt; StartLine = 1
gt; HowManyLines = .CountOfLines
gt; DeleteLines StartLine, HowManyLines
gt; End With
gt;
gt; I have also tried the following and it still gets hung up.
gt;
gt; Sub DeleteAllVBA()
gt;
gt; Dim VBComp As VBIDE.VBComponent
gt; Dim VBComps As VBIDE.VBComponents
gt;
gt; Set VBComps = ActiveWorkbook.VBProject.VBComponents
gt;
gt; For Each VBComp In VBComps
gt; Select Case VBComp.Type
gt; Case vbext_ct_StdModule, vbext_ct_MSForm, _
gt; vbext_ct_ClassModule
gt; VBComps.Remove VBComp
gt; Case Else
gt; With VBComp.CodeModule
gt; DeleteLines 1, .CountOfLines
gt; End With
gt; End Select
gt; Next VBComp
gt;
gt; End Sub
gt;
gt; I have checked the quot;Microsoft Visual Basics for Applications
gt; Extensibilityquot; box.
gt;
gt; Any help??
gt;
gt; Thank you
gt;
gt;
gt; --
gt; Paige
gt; ------------------------------------------------------------------------
gt; Paige's Profile:
gt; www.excelforum.com/member.php...oamp;userid=23096
gt; View this thread: www.excelforum.com/showthread...hreadid=505912
gt;

Thank you!! All those settings we have to remember when getting an
upgrade. So irritating!! Thank you very much for your help!!--
Paige
------------------------------------------------------------------------
Paige's Profile: www.excelforum.com/member.php...oamp;userid=23096
View this thread: www.excelforum.com/showthread...hreadid=505912