问将工作表VBA转换为所有工作表

EN

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sh
        Dim OldVal As String
        Dim NewVal As String
    
        ' If more than 1 cell is being changed
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        If Not Intersect(Target, ActiveSheet.Range("Named_Range")) Is Nothing Then
            ' Turn off events so our changes don't trigger this event again
            Application.EnableEvents = False
            NewVal = Target.Value
            
            ' If there's nothing to undo this will cause an error
            On Error Resume Next
            Application.Undo
            On Error GoTo 0
            OldVal = Target.Value
            ' If selection is already in the cell we want to remove it
            
            If InStr(OldVal, NewVal) Then
                'If there's a comma in the cell, there's more than one word in the cell
                If InStr(OldVal, ",") Then
                    If InStr(OldVal, ", " & NewVal) Then
                        Target.Value = Replace(OldVal, ", " & NewVal, "")
                    Else
                        Target.Value = Replace(OldVal, NewVal & ", ", "")
                    End If
                Else
                    ' If we get to here the selection was the only thing in the cell
                    Target.Value = ""
                End If
            
            Else
                If OldVal = "" Then
                    Target.Value = NewVal
                Else
                    ' Delete cell contents
                    If NewVal = "" Then
                        Target.Value = ""
                    Else
                        ' This IF prevents the same value appearing in the cell multiple times
                        ' If you are happy to have the same value multiple times remove this IF
                        If InStr(Target.Value, NewVal) = 0 Then
                            Target.Value = OldVal & ", " & NewVal
                        End If
                    End If
                End If
            End If
            Application.EnableEvents = True
                
        Else
            Exit Sub
        End If
        
    End With
End Sub