How about adding it at the beginning :
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
'Define your variables.
Dim ws As Worksheet, EvalRange As Range
'Set the range where you want to prevent duplicate entries.
Set EvalRange = Range("A1:A60")
if target.value<1000000 or target.value> 3999999 then msgbox "Value out of range"
'or similarily to duplicate also undo
' if target.value<1000000 or target.value> 3999999 then
' MsgBox Target.Value & " out of allowed range"
' Application.EnableEvents = False
' Application.Undo
' Application.EnableEvents = True
' End If
'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 & " - the bilagsnr already exists on this sheet."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
'...
By the way - why not use just data validation in the spreadsheet?
Bookmarks