VBA find replace object error

[mrcw]

I'm trying to find and replace a specific piece of code in 70 different code objects (macros tied to different worksheets). When I run the code below I get an object error and it highlights 'Set WksName = ws.Name' I got a code from Leith Ross in a separate thread for changing things on individual sheets that helped me out astronomically but now I want to change code on all my sheets and this is my attempt at adapting his code.

    Dim Code    As String    Dim VBComp  As Object
    Dim VBProj  As Object
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim WksName As String
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    For Each ws In wb.Worksheets
    Set WksName = ws.Name
    Set VBProj = Application.VBE.ActiveVBProject
    Set VBComp = VBProj.VBComponents(WksName)
     
    With CodeModule
        VBComp.Code = .Lines(1, .CountOfLines)
        Code=Replace(Code,"find","replace")
        .DeleteLines 1, .CountOfLines
        .InsertLines 1, Code
    End With
    ws.visible = xlSheetHidden
    Next ws

Thanks in advance for any and all help.

[YasserKhalil]

Hello
can you attach sample of your workbook?

[mrcw]

It unfortunately has a large amount of sensitive information... let me see if I can't create a slimmed down copy with false information.

[mrcw]

I made a separate document that should be all you need. If you can get the "Replace This" line in the macros connected to sheets 1, 2 & 3 to automatically change to "Nice Work" with the code in module 1 you will have figured it out. Make sure to enable Microsoft Visual Basice for Applications Extensibility 5.3 in your references.

Please let me know if you have any follow up questions or if you feel like you're onto something but are getting a different error message.

[YasserKhalil]

Hello Try this code based on Mr. Leith Ross's code

'Reference : Microsoft Visual Basice for Applications Extensibility 5.3
'----------------------------------------------------------------------


Sub ChangeMacro()
    Dim Wb          As Workbook
    Dim Ws          As Worksheet
    Dim VBComp      As Object
    Dim VBProj      As Object
    Dim Code        As String
    Dim WksName     As String


    Set Wb = ActiveWorkbook


    For Each Ws In Wb.Worksheets
        WksName = Ws.CodeName
        Set VBProj = Application.VBE.ActiveVBProject
        Set VBComp = VBProj.VBComponents(WksName)


        With VBComp.CodeModule
            Code = .Lines(1, .CountOfLines)
            Code = Replace(Code, "Replace This", "Nice Work!")
            .DeleteLines 1, .CountOfLines
            .InsertLines 1, Code
        End With


        'There Is No Way To Hide All The Sheets In The Workbook
        'ws.Visible = xlSheetHidden
    Next Ws
    
    MsgBox "Done...", 64
End Sub

[mrcw]

Was exactly what I was looking for, thanks a bunch!

[YasserKhalil]

You're welcome. Glad I can offer some help
And many thanks for Mr. Leith Ross (Credits go to him ..)

[mrcw]

Any idea why I would get an "Invalid procedure call or argument" error when I try to take this to my full workbook? I'm trying to find and replace "Z:\Contracts\XYZ\GX Generics\COMLogo.png" with "T:\COMMON\GX Generics\Files for Program\COMlogo.png"

Also tried slimming the find and replace items to "Z:\Contracts\XYZ\" and "T:COMMON" but still no luck

[YasserKhalil]

Have you tried that line

Code = Replace(Code, "Z:\Contracts\XYZ\GX Generics\COMLogo.png", "T:\COMMON\GX Generics\Files for Program\COMlogo.png")

It is better to refer to the line where you got the error .. bu clicking Debug and copy for us the yellow line

[mrcw]

I did try that.. unfortunately couldn't debug it for some reason. Here is the error box that popped up.
Error Code.PNG

The really weird part is that it changed everything despite the error code showing up.

[YasserKhalil]

May be the error appears for the second time .. when the code found nothing to replace ..!!