避免範圍中有重複項目

下列程式碼範例會驗證在範圍 A1:B20 中輸入的值是否存在於目前活頁簿中任何工作表的範圍內，可避免項目重複。

範例程式碼提供者：Holy Macro! Books 所出版的 Holy Macro! It's 2,500 Excel VBA Examples

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'Define your variables.
    Dim ws As Worksheet, EvalRange As Range
    
    'Set the range where you want to prevent duplicate entries.
    Set EvalRange = Range("A1:B20")
    
    'If the cell where value was entered is not in the defined range, if the value pasted is larger than a single cell,
    'or if no value was entered in the cell, then exit the macro.
    If Intersect(Target, EvalRange) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    If IsEmpty(Target) Then Exit Sub
    
    'If the value entered already exists in the defined range on the current worksheet, throw an
    'error message and undo the entry.
    If WorksheetFunction.CountIf(EvalRange, Target.Value) > 1 Then
        MsgBox Target.Value & " already exists on this sheet."
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
    
    'Check the other worksheets in the workbook.
    For Each ws In Worksheets
        With ws
            If .Name <> Target.Parent.Name Then
                'If the value entered already exists in the defined range on the current worksheet, throw an
                'error message and undo the entry.
                If WorksheetFunction.CountIf(Sheets(.Name).Range("A1:B20"), Target.Value) > 0 Then
                    MsgBox Target.Value & " already exists on the sheet named " & .Name & ".", _
                    16, "No duplicates allowed in " & EvalRange.Address(0, 0) & "."
                    Application.EnableEvents = False
                    Application.Undo
                    Application.EnableEvents = True
                    Exit For
                End If
            End If
        End With
    Next ws

End Sub

關於參與者

Holy Macro! 專為使用 Microsoft Office 的人所發行的有趣書籍。 請於下列網站參閱完整目錄：MrExcel.com.

支援和意見反應

有關於 Office VBA 或這份文件的問題或意見反應嗎？ 如需取得支援服務並提供意見反應的相關指導，請參閱 Office VBA 支援與意見反應。