Deleting Workbook_Open code

[Paige]

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 "Microsoft Office Basic Edition 2003" 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("ThisWorkbook").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 "Microsoft Visual Basics for Applications Extensibility" box.

Any help??

Thank you

[Dave Peterson]

Is 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:
>
> 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 "Microsoft Office
> Basic Edition 2003" 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("ThisWorkbook").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 "Microsoft Visual Basics for Applications
> Extensibility" box.
>
> Any help??
>
> Thank you
>
> --
> Paige
> ------------------------------------------------------------------------
> Paige's Profile: http://www.excelforum.com/member.php...o&userid=23096
> View this thread: http://www.excelforum.com/showthread...hreadid=505912

--

Dave Peterson

[Bernie Deitrick]

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 = "'" & .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 MVP


"Paige" <[email protected]> wrote in message
news:[email protected]...
>
> 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 "Microsoft Office
> Basic Edition 2003" 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("ThisWorkbook").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 "Microsoft Visual Basics for Applications
> Extensibility" box.
>
> Any help??
>
> Thank you
>
>
> --
> Paige
> ------------------------------------------------------------------------
> Paige's Profile:
> http://www.excelforum.com/member.php...o&userid=23096
> View this thread: http://www.excelforum.com/showthread...hreadid=505912
>

[Paige]

Thank you!! All those settings we have to remember when getting an upgrade. So irritating!! Thank you very much for your help!!