DELETE A VBA SUBROUTINE FROM A CODE MODULE LIST (SOLVED)

[BrianB]

** Please do not add replies here. Use the original message **
I recently supplied code in answer to the requirement above. In June I wrote a macro to extract module names & subroutines to a worksheet, so it seems a good idea to link the two and enable deletion of a subroutine from a selection in the worksheet.

'=========================================================================================
'- MACRO TO DELETE A SUBROUTINE FROM A VBA CODE MODULE
'=========================================================================================
'- 1. List all subroutines in a workbook using "SHOW_ALL_MODULES" code found here :-
'-    http://www.mrexcel.com/board2/viewtopic.php?t=276388&highlight=
'- 2. Select a subroutine name - eg. 'MySubRoutine ()' - in the worksheet 'WB Contents'
'-    (the worksheet must be in the workbook containing the sub)
'- 3. Run macro "DeleteSelectedSubroutine"
'-
'- Check Tools/References/"Microsoft Visual Basic for Applications Extensibility"
'- Brian Baulsom August 2007
'==========================================================================================



'===================================================================================
'- MAIN ROUTINE
'===================================================================================
Sub DeleteSelectedSubroutine()
    Dim MySub As String
    Dim MyModuleName As String
    '-------------------------------------------------------------------------------
    '- get selection
    MySub = ActiveCell.Value
    MyModuleName = ActiveCell.Offset(0, -1).Value
    '-------------------------------------------------------------------------------
    '- check valid selection
    If Right(MySub, 2) <> "()" Or ActiveSheet.Name <> "WB Contents" Then
        MsgBox ("You have not selected a valid subroutine in 'WB Contents' sheet" & vbCr _
            & "Subroutine : " & MySub & vbCr & "Module       : " & ModuleName)
            Exit Sub
    End If
    '--------------------------------------------------------------------------------
    '- delete subroutine
    DeleteSubroutine MyModuleName, MySub
End Sub
'========= END OF MAIN ROUTINE ===================================================



'=================================================================================
'- SUBROUTINE : CALLED FROM MAIN ROUTINE
'=================================================================================
Private Sub DeleteSubroutine(ModuleName, SubName)
    Dim MyModule As Object
    Dim MyLineNumber As Integer
    Dim MyLine As String
    Dim StartLine As Integer
    Dim EndLine As Integer
    Dim MySubLines As Integer
    '-----------------------------------------------------------------------------
    Set MyModule = ActiveWorkbook.VBProject.vbComponents(ModuleName).codemodule
    MyLineNumber = 1
    With MyModule
        '-----------------------------------------------------------------------------
        '- Find subroutine
        '-----------------------------------------------------------------------------
        For MyLineNumber = 1 To .countoflines
            MyLine = .Lines(MyLineNumber, 1)
            If InStr(1, MyLine, SubName, vbTextCompare) > 0 Then
                StartLine = MyLineNumber
                Exit For
            End If
        Next
        '--------------------------------------------------------
        '- check subroutine found
        If MyLineNumber >= .countoflines Then
            MsgBox ("Cannot find Sub " & SubName & "()" & vbCr _
                  & "in module '" & ModuleName & "'")
            Exit Sub
        End If
        '-----------------------------------------------------------------------------
        '- Find End Sub
        '-----------------------------------------------------------------------------
        While InStr(1, MyLine, "End Sub", vbTextCompare) = 0
            MyLineNumber = MyLineNumber + 1
            MyLine = .Lines(MyLineNumber, 1)
        Wend
        EndLine = MyLineNumber + 1
        '-----------------------------------------------------------------------------
        '- delete lines
        '-----------------------------------------------------------------------------
        MySubLines = EndLine - StartLine
        .DeleteLines StartLine, MySubLines
    End With
    '----------------------------------------------------------------------------------
    MsgBox ("Deleted Sub " & SubName & " ( )" & vbCr _
          & "from module '" & ModuleName & "'" & vbCr & "= " & MySubLines & " lines." _
          & vbCr & "Save the workbook to make change permanent.")
End Sub
'----------- end of sub routine --------------------------------------------------------