多列不同下拉列表的Excel VBA防非法粘贴代码优化需求

Absolutely—using a function-driven approach is not only possible but a smart move here. It’ll make your code way cleaner, easier to update, and trivial to extend if you add more columns down the line. Let’s refactor your existing code to use reusable helper functions, then call those functions from the Worksheet_Change event to handle multiple columns.

Refactored Code with Function Calls #

First, we’ll create two helper functions to handle different validation types (list-based and text-length, matching your original code’s logic). Then we’ll simplify the Worksheet_Change event to call these functions as needed.

Step 1: Add Helper Functions

Paste these above your Worksheet_Change event:

' Helper function for list-based dropdown validation
Private Sub ValidateListColumn(ByVal TargetRange As Range, ByVal ValidationColumn As Range, ByVal DropdownSource As Range)
    Dim dd() As Variant
    Dim i As Long
    Dim mtch As Boolean
    Dim cell As Range
    Dim msg As String
    Dim myEntries As String
    
    ' Load dropdown options into an array for fast checking
    ReDim dd(DropdownSource.Cells.Count - 1)
    i = 0
    For Each cell In DropdownSource
        dd(i) = cell.Value
        i = i + 1
    Next cell
    
    ' Validate each changed cell in the target column
    For Each cell In Intersect(ValidationColumn, TargetRange)
        mtch = False
        If cell.Value <> "" Then
            For i = LBound(dd) To UBound(dd)
                If cell.Value = dd(i) Then
                    mtch = True
                    Exit For
                End If
            Next i
        End If
        
        ' Clear invalid entries and track errors
        If Not mtch Then
            cell.ClearContents
            msg = msg & cell.Address(0, 0) & ", "
        End If
    Next cell
    
    ' Rebuild the dropdown validation rule
    myEntries = ""
    For i = LBound(dd) To UBound(dd)
        myEntries = myEntries & dd(i) & ","
    Next i
    myEntries = Left(myEntries, Len(myEntries) - 1)
    
    With ValidationColumn.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=myEntries
    End With
    
    ' Show error summary if needed
    If Len(msg) > 0 Then
        MsgBox "Invalid entries in cells: " & vbCrLf & Left(msg, Len(msg) - 2), vbOKOnly, "ERROR!"
    End If
End Sub

' Helper function for text-length validation (matches your original D-column logic)
Private Sub ValidateTextLengthColumn(ByVal TargetRange As Range, ByVal ValidationColumn As Range, ByVal RequiredLength As Integer)
    Dim cell As Range
    Dim msg As String
    
    ' Validate each changed cell in the target column
    For Each cell In Intersect(ValidationColumn, TargetRange)
        If Len(cell.Value) > 0 And Len(cell.Value) <> RequiredLength Then
            cell.ClearContents
            msg = msg & cell.Address(0, 0) & ", "
        End If
    Next cell
    
    ' Rebuild the text-length validation rule
    With ValidationColumn.Validation
        .Delete
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=CStr(RequiredLength)
    End With
    
    ' Show error summary if needed
    If Len(msg) > 0 Then
        MsgBox "Invalid entries in cells: " & vbCrLf & Left(msg, Len(msg) - 2), vbOKOnly, "ERROR!"
    End If
End Sub

Step 2: Simplify the Worksheet_Change Event

Replace your original Worksheet_Change event with this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngC As Range
    Dim rngD As Range
    Dim ddRangeC As Range
    Dim ddRangeD As Range
    
    ' Define your target columns and their dropdown sources
    Set rngC = Me.Range("C:C")
    Set rngD = Me.Range("D:D")
    Set ddRangeC = ThisWorkbook.Sheets("Dropdowns").Range("A2:A11")
    Set ddRangeD = ThisWorkbook.Sheets("Dropdowns").Range("B2:B8")
    
    Application.EnableEvents = False
    
    ' Handle column C (list validation)
    If Not Intersect(rngC, Target) Is Nothing Then
        ValidateListColumn Target, rngC, ddRangeC
    End If
    
    ' Handle column D: choose one option below
    If Not Intersect(rngD, Target) Is Nothing Then
        ' Option 1: Use list validation from Dropdowns!B2:B8 (as per your question description)
        ValidateListColumn Target, rngD, ddRangeD
        
        ' Option 2: Keep original text-length validation (11 characters)
        ' ValidateTextLengthColumn Target, rngD, 11
    End If
    
    Application.EnableEvents = True
End Sub

Key Improvements & Notes #

• Modularity: The helper functions separate validation logic from the event handler, so you can reuse them for any new columns by just adding a new call in Worksheet_Change.
• Bug Fixes: Fixed array indexing and trailing commas in error messages that were present in your original code.
• Flexibility: For column D, we included both options (list validation or text-length check) — just uncomment the line that matches your needs.
• Maintainability: All column and source ranges are defined at the top of the event, making it easy to adjust later.

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