G'day Ken

I use the following to check & kill any codes or modules in a workbook.

Now, I know you don't want to kill the coding, but you maybe able to
alter it to suit your purposes.

I have no idea how it works as I found it online, but it does what I
need it to do.

HTH
Mick. Melbourne.


Sub KillVBCode()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim StartLine As Long
Dim NumLines As Long
Dim ProcName As String
Dim WillRobinson As Integer

WillRobinson = MsgBox("[ DANGER WILL ROBINSON ] YOU ARE ABOUT
TO DELETE ALL VITAL CODES FROM THIS FILE, YA REALLY WANNA DO
THAT..????", vbYesNo)
If WillRobinson = vbYes Then

With Application.VBE
If Not .ActiveCodePane Is Nothing Then
Set .ActiveVBProject =
..ActiveCodePane.CodeModule.Parent.Collection.Pare nt
End If
End With

Call StopTimer

Set VBProj = ActiveWorkbook.VBProject

Set VBComp = VBProj.VBComponents("Module1")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module2")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module3")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module4")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module5")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module6")
VBProj.VBComponents.Remove VBComp

Rows("1:3").Select
Selection.Delete Shift:=xlUp

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If

Next VBComp

Else
Cancel = True
End If

Range("A1").Select

End Sub

Function IsEditorInSync() As Boolean
'================================================= ======================
' IsEditorInSync
' This tests if the VBProject selected in the Project window, and
' therefore the ActiveVBProject is the same as the VBProject associated
' with the ActiveCodePane. If these two VBProjects are the same,
' the editor is in sync and the result is True. If these are not the
' same project, the editor is out of sync and the result is True.
'================================================= ======================
With Application.VBE
IsEditorInSync = .ActiveVBProject Is _
.ActiveCodePane.CodeModule.Parent.Collection.Paren t
If IsEditorInSync = True Then
Debug.Print
End If
End With
End Function