VBA实现多合并单元格点击切换Yes/Blank状态求助

Got it, let's get this sorted out! You've got a working setup for the merged cell R33:S33, and now you just need to extend that functionality to the other merged ranges (R35:S35, R37:S37, etc.). Here's how to modify your Worksheet_SelectionChange subroutine to handle all these cells at once:

Modified VBA Code #

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Define all the merged cell ranges you want to handle
    Dim targetMergedRanges As Range
    Set targetMergedRanges = Union( _
        Range("R33:S33"), _
        Range("R35:S35"), _
        Range("R37:S37"), _
        Range("R39:S39"), _
        Range("R41:S41") _
    )
    
    ' Check if the clicked cell is within any of our target merged ranges
    If Not Intersect(Target, targetMergedRanges) Is Nothing Then
        ' Get the full merged area (in case the user clicks part of the merged cell)
        Dim mergedArea As Range
        Set mergedArea = Target.MergeArea
        
        ' Toggle between blank and "Yes"
        If mergedArea.Value = "" Then
            mergedArea.Value = "Yes"
        Else
            mergedArea.Value = ""
        End If
    End If
End Sub

Key Details to Note: #

• Union Function: This lets you group multiple non-contiguous ranges into a single Range object, so you don't have to write separate If conditions for each merged cell.
• Target.MergeArea: When someone clicks any part of a merged cell, this ensures we're modifying the entire merged region instead of just the individual cell that was clicked.
• Easy Scalability: If you need to add more merged cells later (like R43:S43), just add another Range("XX:XX") inside the Union parentheses.

This keeps your code clean, maintainable, and does exactly what you need—toggling the value between blank and "Yes" for all your specified merged cells when clicked.

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