通过数据验证，防止将具有数据验证的列中的单元格复制并粘贴到另一列

1 回答

• 1

  您可以在更改后检查单元格是否仍有验证 .

  使用Workbook更改事件，在ThisWorkbook模块中

  Option Explicit
  
  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'Does the validation range still have validation?
  If Not HasValidation(Range("RangeToProtect1")) Then RestoreValidation
  If Not HasValidation(Range("RangeToProtect2")) Then RestoreValidation
  If Not HasValidation(Range("RangeToProtect3")) Then RestoreValidation
  If Not HasValidation(Range("RangeToProtect4")) Then RestoreValidation
  End Sub
  
  Private Sub RestoreValidation()
  Application.EnableEvents = False
  'turn off events so this routine is not continuously fired
  Application.Undo
  Application.EnableEvents = True
  'and turn them on again so we can catch the change next time
  MsgBox "Your last operation was canceled." & _
         "It would have deleted data validation rules.", vbCritical
  End Sub
  
  Private Function HasValidation(r) As Boolean
  '   Returns True if every cell in Range r uses Data Validation
  On Error Resume Next
  Debug.Print r.Validation.Type    'don't care about result, just possible error
  If Err.Number = 0 Then HasValidation = True Else HasValidation = False
  End Function
  

  在 RangeToProtect 中，您可以指定特定范围，也可以使用命名范围（请注意，命名范围虽然使代码易于阅读，但如果删除了您要保护的整个范围，则会导致代码失败）

  回复于 2024-04-23T19:13:58+08:00