Excel VBA：点击指定单元格无法触发表格显隐的问题求助

Hey there! Let's troubleshoot why your Worksheet_Change event isn't triggering to show/hide those ListObject tables. I’ve run into this exact scenario before, so let’s break down the fixes step by step.

First: Why Your Worksheet_Change Might Not Be Firing #

The most common culprit here is how you’re modifying cell values in Worksheet_SelectionChange. If you don’t handle event enablement properly, you might either accidentally disable events entirely or the cell change won’t register as a trigger for Worksheet_Change. Another possibility is that your Worksheet_Change code has silent errors or isn’t targeting the right cells.

Fix 1: Correct Your Worksheet_SelectionChange Code #

When you toggle cell values in this sub, you need to temporarily disable events to avoid infinite loops (since changing a cell value would re-trigger SelectionChange), then re-enable them so Worksheet_Change can fire. Here’s a revised version:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Define the cells you want to toggle (update ranges/names to match your sheet)
    Dim toggleCells As Range
    Set toggleCells = Me.Range("Point_load, Line_load")
    
    ' Only run if the selected cell is one of our toggle targets
    If Not Intersect(Target, toggleCells) Is Nothing Then
        Application.EnableEvents = False ' Prevent event loop
        
        ' Toggle Yes/No value
        Target.Value = IIf(Target.Value = "Yes", "No", "Yes")
        
        ' Apply your custom formatting (adjust to match your needs)
        With Target
            If .Value = "Yes" Then
                .Interior.Color = RGB(146, 208, 80) ' Soft green
                .Font.Bold = True
            Else
                .Interior.Color = RGB(255, 199, 206) ' Soft red
                .Font.Bold = False
            End If
        End With
        
        Application.EnableEvents = True ' Re-enable events so Worksheet_Change can trigger
    End If
End Sub

Fix 2: Write a Robust Worksheet_Change Event #

Now let’s build a reliable Worksheet_Change sub that checks for your toggle cell updates and controls the tables. We’ll add error handling to ensure events never stay disabled if something goes wrong:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False ' Disable events temporarily
    On Error GoTo Cleanup ' Ensure events get re-enabled even if code fails
    
    ' Check which toggle cell was changed and update the corresponding table
    Select Case Target.Address(False, False) ' Use relative address without $ signs
        Case "Point_load" ' Match your cell's exact address/named range
            Me.ListObjects("Point_load_section").Visible = (Target.Value = "Yes")
        Case "Line_load"
            Me.ListObjects("Line_load_section").Visible = (Target.Value = "Yes")
        ' Add more cases here for other toggle cells (e.g., "Distributed_load")
    End Select

Cleanup:
    Application.EnableEvents = True ' Always re-enable events!
End Sub

Quick Checks to Ensure Everything Works #

1. Verify Table Names: Go to the Developer tab → Design Mode → Select your table → Check the Name box in the Table Tools > Design ribbon to make sure it exactly matches what you’re using in code (no typos!).
2. Enable Macros: Ensure your workbook has macros enabled (File → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros, or use digital signatures for safer access).
3. Check Code Location: Both subs must be in the worksheet’s code module (right-click the worksheet tab → View Code) — not a standard module.

Once you’ve updated the code, test it by clicking your Point_load or Line_load cell: it should toggle Yes/No, update formatting, and immediately show/hide the corresponding table.

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