藉由「正規表示法」來比對特殊字元,完成此項功能。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "工作表1" Then If Target.Column = 4 Then 執行 Target End If End If End Sub Sub 執行(Target As Range) Dim regex As Object, rangecell As Object Dim row As Integer, col As Integer col = Target.Column row = Sheets(1).Cells(65536, col).End(xlUp).row Set regex = CreateObject("VBSCRIPT.REGEXP") Set rangecell = Sheets(1).Cells(2, col).Resize(row - 1) With regex .Pattern = "[\x28\x29\x5C\x2F\x3A\x2A\x3F\x22\x3C\x3E\x7C|\s]+" .Global = True For Each s In rangecell If .test(s) Then MsgBox "儲存格 " & Target.Address(False, False) & " 輸入錯誤文字 " & "\/:*?""<>|" Set rangecell = Nothing Set regex = Nothing Exit For End If Next End With Set rangecell = Nothing Set regex = Nothing End Sub執行結果:
沒有留言:
張貼留言