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.
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