excel vba 2013 how to scroll through userforms and change text in all of them

Question

Good day. We are moving our programs and files to a new folder. Is there a way to scroll through all the userforms to change the default path that is coded in almost all the userforms? Or is there a way to keep the default path in, say, a macro or other module that all the userform codes can read (then I only have to change the path once)?

Les

Answer 1

Add this code to a new macro-enabled workbook. Set the FIND_WHAT and REPLACE_WITH constants, open the other workbooks and run the code.

The original code comes from [Charles Pearson's site][1]

WARNING: Only basic testing has been done!

Option Explicit

Sub ReplaceTextInCodeModules()

' Must add a reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
' Also must set "Trust access to the VBA project object model"
' See the url below for more info on these.
' Based on code found at:
' Source: www.cpearson.com/excel/vbe.aspx Copyright 2013, Charles H. Pearson

Dim theWorkbook As Workbook
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim numLines As Long ' end line
Dim lineNum As Long
Dim thisLine As String
Dim message As String
Dim numFound As Long

Const FIND_WHAT As String = "findthis"
Const REPLACE_WITH As String = "replaced"

    numFound = 0

    For Each theWorkbook In Application.Workbooks
        If theWorkbook.Name <> ThisWorkbook.Name Then
            If theWorkbook.HasVBProject Then
                Set VBProj = theWorkbook.VBProject
                For Each VBComp In VBProj.VBComponents
                    'Set VBComp = VBProj.VBComponents("Module1")
                    Set CodeMod = VBComp.CodeModule

                    With CodeMod
                        numLines = .CountOfLines
                        For lineNum = 1 To numLines
                            thisLine = .Lines(lineNum, 1)
                            If InStr(1, thisLine, FIND_WHAT, vbTextCompare) > 0 Then
                                message = message & theWorkbook.Name & " | " & VBComp.Name & " | Line #" & lineNum & vbNewLine
                                .ReplaceLine lineNum, Replace(thisLine, FIND_WHAT, REPLACE_WITH, , , vbTextCompare)
                                numFound = numFound + 1
                            End If
                        Next lineNum
                    End With
                Next VBComp
            End If
        End If
    Next theWorkbook

    Debug.Print "Found: " & numFound
    If message <> "" Then
        Debug.Print message
    End If

End Sub

Answer 2

Tim, That was a simple and easy enough solution, thank you. I declared the 'path' as a string rather than a constant to allow future changes (my company makes too many changes). I have a maintenance userform for other things and added an input button that asks the user for a 'path'. I put a flag in the 'pathname' sub that checks if the sub was called from the maintenance module. If the 'pathname' sub wasn't called from the maintenance module then it simply returns the present 'path' to the calling module. And I changed the code in all the userform modules (that reference the path) to call the sub.