Error handler question in Worksheet_Change event

[Sam Kuo]

Hi

My code below clears a cell's value then prompts an error message, if the
input value is outside the specified limit.
But I'd prefer to reverse the sequence (i.e. prompts the error message
first, then clear the input upon accpeting the error message) but don't know
how...

Any help is appreciated.

Sam


Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyWkSht As Worksheet
Dim ARIRange As Range

Set MyWkSht = ThisWorkbook.Worksheets("Sheet1")
Set ARIRange = MyWkSht.Range("N13")

MyWkSht.Unprotect (1)

' Other codes

If ARIRange.Value < 50 Or ARIRange.Value > 130 Then
ARIRange.Value = ""
MyWkSht.Protect (1)
GoTo ErrorHandler
Exit Sub

ErrorHandler:
MsgBox "Please enter a value between 50mm and 130mm.", vbOKOnly,
"Warning"
End

Else
End If

MyWkSht.Protect (1)

End Sub

 

[Sam Kuo]

Also, is there a better place (other than Worksheet_Change event) to put this
code under, so it doesn't alway trigger the error message when the cell (N13
in this case) is blank?

 

[RadarEye]

Hi Sam,

Try this:

' Begin of code ------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$13" Then
If Not IsEmpty(Target) Then
If IsNumeric(Target.Value) Then
If Target.Value < 50 Or Target.Value > 130 Then
MsgBox "Please enter a value between 50mm and
130mm.", vbOKOnly + vbInformation
Target.ClearContents
End If
Else
MsgBox "Please enter a value between 50mm and 130mm.",
vbOKOnly + vbInformation
Target.ClearContents
End If
End If
End If
End Sub
' End of code ------------------------

HTH,

Execurot

 

[Sam Kuo]

Thanks RedarEye. It works great!
I've also made the following change - because the input cell is a merged
cell (i.e. N13:O13), but Excel doesn't seem to allow ClearContents in a
merged cell when a cell (i.e. O13) is empty.

change:
Tartget.ClearContents

to:
ThisWorkbook.Worksheets("Sheet1").Range("N13").ClearContents


"Target.ClearContents"