Excel宏开发需求：整列触发工作表显隐及单词关联多表显示

Here's how to adjust your code to support the entire Column C and allow single keywords to show multiple associated sheets:

Key Changes Explained #

• Column-Wide Support: Checks if any modified cell is in Column C using Intersect(Target, Me.Columns("C"))
• Keyword-to-Sheet Mapping: Uses a dictionary to define which keywords correspond to which sheets, making it easy to update or add new mappings later
• Case Insensitivity: Converts the cell value to uppercase so inputs like "motor" or "Motor" work the same as "MOTOR"
• Error Handling: Includes checks to avoid runtime errors if a sheet name is misspelled or doesn't exist

Full Modified Code #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Disable events to prevent infinite loops during sheet visibility changes
    Application.EnableEvents = False
    
    ' Define the range we care about: entire Column C
    Dim watchRange As Range
    Set watchRange = Intersect(Target, Me.Columns("C"))
    
    If Not watchRange Is Nothing Then
        ' Create a dictionary to map keywords to their associated sheets
        Dim sheetMap As Object
        Set sheetMap = CreateObject("Scripting.Dictionary")
        
        ' Populate the mapping (add or modify entries as needed)
        sheetMap("POMP") = Array("POMP")
        sheetMap("TANK") = Array("TANK")
        sheetMap("VENTILATOR") = Array("VENTILATOR")
        sheetMap("MOTOR") = Array("MOTOR", "POMP") ' MOTOR shows both MOTOR and POMP
        
        ' First, hide all relevant sheets to reset visibility
        Dim allSheets As Variant
        allSheets = Array("POMP", "TANK", "VENTILATOR", "MOTOR")
        
        Dim sheetName As Variant
        For Each sheetName In allSheets
            On Error Resume Next ' Ignore if sheet doesn't exist
            ThisWorkbook.Sheets(sheetName).Visible = False
            On Error GoTo 0 ' Reset error handling
        Next sheetName
        
        ' Process each modified cell in Column C
        Dim cell As Range
        For Each cell In watchRange
            Dim keyword As String
            keyword = UCase(Trim(cell.Value)) ' Normalize input to uppercase
            
            ' Check if the keyword exists in our mapping
            If sheetMap.Exists(keyword) Then
                ' Show all sheets associated with this keyword
                Dim targetSheet As Variant
                For Each targetSheet In sheetMap(keyword)
                    On Error Resume Next
                    ThisWorkbook.Sheets(targetSheet).Visible = True
                    On Error GoTo 0
                Next targetSheet
            End If
        Next cell
    End If
    
    ' Re-enable events
    Application.EnableEvents = True
End Sub

How to Use #

1. Open your Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Find the worksheet where you want this functionality (the one with Column C inputs) in the Project Explorer
4. Paste this code into the code window for that worksheet
5. Save your workbook as a Macro-Enabled Workbook (.xlsm)

Notes #

• If you want to add more keywords or sheet associations, just update the sheetMap entries (e.g., sheetMap("NEW_KEYWORD") = Array("Sheet1", "Sheet2"))
• The code hides all relevant sheets first before showing the ones matching the keyword—this ensures only the correct sheets are visible after each change
• Case doesn't matter for input (e.g., "motor", "Motor", and "MOTOR" all trigger the same sheet visibility)

内容的提问来源于stack exchange，提问作者Jessica Van Breda