|
用VBA工作表事件的方法,防止
C4:M4这个区域有重复值输入,
当有重复值输入时,弹出提示框“该区域禁止输入重复值”,并且光标返回到当前单元格消除当前单元格的重复值。
见附件,请老师们帮忙,写个VBA工作表事件。
谢谢大家!!!
把以下代码放入表三中
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim rng As Range
- Dim rngTemp As Range
- Dim b As Boolean
- Application.EnableEvents = False
-
- Set rng = Application.Union(Range("C4:M4"), Range("F8:P8"), Range("J12:L15"), Range("H15:H23"))
- b = True
- If Not Application.Intersect(Target, rng) Is Nothing Then
- For Each rng In Range("C4:M4")
- If Target.Address <> rng.Address And rng = Target Then
- b = False
- Exit For
- End If
- Next
-
- For Each rng In Range("F8:P8")
- If Target.Address <> rng.Address And rng = Target Then
- b = False
- Exit For
- End If
- Next
-
- For Each rng In Range("J12:L15")
- If Target.Address <> rng.Address And rng = Target Then
- b = False
- Exit For
- End If
- Next
-
- For Each rng In Range("H15:H23")
- If Target.Address <> rng.Address And rng = Target Then
- b = False
- Exit For
- End If
- Next
- End If
- If b = False Then
- MsgBox "该区域禁止输入重复值"
- Target = ""
- Target.Select
- End If
- Application.EnableEvents = True
- End Sub
复制代码
详见附件:
VBA工作表事件,防止指定区域有重复值输入.rar
(12.59 KB, 下载次数: 110)
|
|