如何查询指定Sub是否关联Excel UserForm中的InputBox

Got it, dealing with messy legacy VBA code left by a former colleague is such a common pain point—especially when you’ve got 24 Subs and 12 UserForms/InputBoxes to untangle. Instead of manually checking every InputBox to see which Subs it calls, here are three straightforward ways to quickly look up if a specific Sub is linked to any InputBox or UserForm:

This is the fastest way for one-off checks:

• Open your Excel file, hit Alt + F11 to launch the VBA Editor.
• Press Ctrl + F to pull up the Find dialog.
• In the "Find what" field, type the exact name of the Sub you’re investigating (e.g., UpdateCustomerData).
• Under "Look in", select Current Project to search every module and UserForm in your file.
• Check the "Match whole word" box to avoid false positives (like partial matches in comments or variable names).
• Click "Find All"—the results pane will show every line where the Sub is referenced, including calls from UserForm button clicks, InputBox validation code, or any other place it’s triggered.

If you need to check multiple Subs or want a documented list, this script will auto-create a worksheet with all references:

Sub FindSubToInputBoxLinks()
    Dim vbComp As VBComponent
    Dim codeMod As CodeModule
    Dim lineNum As Long
    Dim totalLines As Long
    Dim lineText As String
    Dim targetSub As String
    Dim ws As Worksheet
    
    ' Ask for the Sub name to search
    targetSub = InputBox("Enter the name of the Sub to check:", "Find Sub Links")
    If targetSub = "" Then Exit Sub
    
    ' Create or reuse a results worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Sub Links")
    If Err.Number <> 0 Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = "Sub Links"
    End If
    On Error GoTo 0
    
    ' Prep the worksheet
    ws.Cells.Clear
    ws.Range("A1:B1").Value = Array("Reference Location", "Line of Code")
    ws.Range("A1:B1").Font.Bold = True
    
    ' Scan all project components
    For Each vbComp In ThisWorkbook.VBProject.VBComponents
        Set codeMod = vbComp.CodeModule
        totalLines = codeMod.CountOfLines
        
        For lineNum = 1 To totalLines
            lineText = codeMod.Lines(lineNum, 1)
            ' Skip the Sub's own definition line
            If InStr(1, lineText, targetSub, vbTextCompare) > 0 And _
               Not InStr(1, lineText, "Sub " & targetSub, vbTextCompare) = 1 Then
                ' Add the reference to the worksheet
                ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = _
                    vbComp.Name & " (Line " & lineNum & ")"
                ws.Cells(ws.Rows.Count, 2).End(xlUp).Offset(1, 0).Value = lineText
            End If
        Next lineNum
    Next vbComp
    
    MsgBox "Done! Check the 'Sub Links' tab for all references.", vbInformation
End Sub

How to use this: #

1. In the VBA Editor, insert a new module (Insert > Module).
2. Paste the code above into the module.
3. Run the FindSubToInputBoxLinks macro. It’ll prompt you to enter the Sub name.
4. A new worksheet will pop up listing every place the Sub is called—including from UserForms, InputBox handlers, and more.

Note: If you get a "Programmatic access to Visual Basic Project is not trusted" error, enable access in Excel: File > Options > Trust Center > Trust Center Settings > Macro Settings > Check "Trust access to the VBA project object model"

For a quick overview of references:

• In the VBA Editor, press F2 to open the Object Browser.
• Type your Sub name into the search bar and hit Enter.
• Look at the "References" section at the bottom—it’ll show all components in your project that reference the Sub. This works great for direct calls, though it might miss dynamic calls (like if the Sub name is stored in a variable).

These methods should cut down your manual work drastically. Start with the Find tool for quick checks, and use the script if you need to document links for multiple Subs.

内容的提问来源于stack exchange，提问作者Luuklag