Add Code To Worksheet with VBA

Originally Posted by romperstomper

If you know how to add code into the ThisWorkbook module, there shouldn't really be much difference adding it to a worksheet module.
The fact that you are having to do this repeatedly suggests to me you might want to rethink whatever it is that you are doing...

Understood, I'll work on some type of code and do some experimenting. This normally happens once a year since I create these workbooks.worksheets from last years templates from the previous year. I'm now working on 2011 workbooks for the year so that is why the change occurs. I just don't want to recreate the workbooks again is all.

I'll keep looking. Thank You,

Originally Posted by romperstomper

If you know how to add code into the ThisWorkbook module, there shouldn't really be much difference adding it to a worksheet module.
The fact that you are having to do this repeatedly suggests to me you might want to rethink whatever it is that you are doing...

This wasn't so Hard After All.

Here is a solution should anyone find the need...

This will loop though all workbooks in a folder and delete the current procedure name and add the same procedure back with new code.

Sub OpenALL()

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
        .NewSearch
         
        .LookIn = "G:\Hazleton Production\2011 Production\Production Links\2011 Large Area"
        .FileType = msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"

    If .Execute > 0 Then

          For lCount = 1 To .FoundFiles.Count
                              
          Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
          
                Call DeleteProcedureFromModule
           
          wbResults.Close SaveChanges:=True
          
          Next lCount
     End If
End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Sub DeleteProcedureFromModule()
Const vbext_pk_Proc As Integer = 0
Const vbext_ct_Document As Integer = 100
    
Dim VBProj As Object
Dim VBComp As Object
Dim CodeMod As Object
Dim StartLine As Long
Dim NumLines As Long
Dim ProcName As String
Dim ws As Object
    
    Set VBProj = ActiveWorkbook.VBProject
    For Each ws In ActiveWorkbook.Worksheets
        Set VBComp = VBProj.VBComponents(ws.CodeName)
        Set CodeMod = VBComp.CodeModule
    
        ProcName = "CommandButton3_Click"
        With CodeMod
            On Error Resume Next
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            If Err.Number = 0 Then
                NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
                .DeleteLines StartLine:=StartLine, Count:=NumLines
            ElseIf Err.Number = 35 Then
                Err.Clear
            End If
        End With
        
        With CodeMod
            On Error Resume Next
             
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Private Sub CommandButton3_Click()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "Workbooks.Open ""G:\Hazleton Production\2011 Production\Production Links\Sheet Lists\2011 Large Area Sheet List.xls"""
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With

    Next
End Sub