Hi,
Ive got a spreadsheet that is essentially calculation based, reporting on a live view of a database. I have a small bit of code in it that allows an offline snapshot copy of itself to be saved so that it can be distributed.
The problem I have is that I want to remove the code from this snapshot to prevent it triggering Excels virus protection. In itself that's not a problem, the difficulty I have is that unless the reference to the VBIDE is removed it still triggers the virus protection.
In itself, the code to remove the reference works fine, but when it is combined witht the code that removes itself I get a compile error.
Any ideas?.
Code
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Sub triggered by the Before Close event that removes the command button and
'removes the code if saving as output file
Call RemoveCommandBtn
If RemoveMyCode = True Then
Call RemoveCode(ThisWorkbook.VBProject.Name)
Call DeleteRefLibraryVBIDE
End If
End Sub
Private Sub RemoveCode(ProjectName As String)
'Sub that parses through all the loaded code modules and removes them if they
'are in the Report V.xls workbook
Dim Project As VBProject
Dim Component As VBComponent
Dim CodeWindow As Variant
Dim line As Integer
Dim linecount As Integer
For Each Project In Application.VBE.VBProjects
If Project.Name = ProjectName Then
For Each Component In Project.VBComponents
If Not Component.CodeModule.CountOfLines = 0 Then
Let linecount = Component.CodeModule.CountOfLines
For line = 1 To linecount
Component.CodeModule.DeleteLines (1)
Next
If Not Component.Type = vbext_ct_Document Then 'this is superfluious now as all the code is in the 'ThisWorkbook' Module
Project.VBComponents.Remove Project.VBComponents(Component.Name)
End If
End If
Next
End If
Next
End Sub
Sub DeleteRefLibraryVBIDE()
'Deletes the reference to the VBIDE
On Error Resume Next
With ThisWorkbook.VBProject.References
.Remove .Item("VBIDE")
End With
End Sub