Error handling in macro

[michaelberrier]

I have a macro that displays a message box if a formula result is true,
and calls another macro if it is false using an "IF..Then..Else" line.
The True/False is based on a specially formatted date being compared to
data in that cell, but there are time when a user will enter a word or
phrase in the comparison box, producing a #VALUE in the cell that the
macro doesn't recognize. I'm guessing this means I will need an error
handler in the macro to deal with that, and I've tried several, but I
can't get it to work. Bottom line, I need the message box to appear if
the value is True(as above and works great), and the macro from Module
22 to run if anything else appears in that cell.
Thanks to all.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub 'only one cell at a time

'exit unless it Is D7 that changed
If Intersect(Target, Me.Range("$D$7")) Is Nothing Then Exit Sub

If IsEmpty(Target.Value) Then Exit Sub
If Range("d4") = True Then
MsgBox "Check Driver's License Expiration Date", 48, "Exprired Driver's
License"
Range("D7").Select
Else
Call Module22.Look_Here1
'the macro To Call when D7 changes



End If

End Sub

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub 'only one cell at a time

'exit unless it Is D7 that changed
If Intersect(Target, Me.Range("$D$7")) Is Nothing Then Exit Sub

If IsEmpty(Target.Value) Then Exit Sub
If Not IsError(Range("D4").Value) Then
If Range("D4") = True Then
MsgBox "Check Driver's License Expiration Date", 48, "Exprired
Driver's License """
Range("D7").Select
Else
'Call Module22.Look_Here1
'the macro To Call when D7 changes
End If
End If

End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Dave O]

Instead of error handling, how about a validation loop early in the
code. This requires valid input before proceeding, and loops until a
numeric (that is to say, a date) value is entered:

Dim UserInput
Do
UserInputLoop:
If Not IsNumeric(Range("a1").Value) Then
UserInput = Inputbox("Please enter a valid date: ")
range("a1").value = UserInput
Goto UserInputLoop:
End If
Loop until IsNumeric(Range("a1").Value)

Constructive criticism: I notice this line has a typo in it: "Exprired"
MsgBox "Check Driver's License Expiration Date", 48, "Exprired Driver's

License"

 

[michaelberrier]

Bob: That error handling stops the error, but the code still doesn't
run if there isn't a date in that cell, and I need that capability.

Dave: I need for the user to be able to enter something besides a date
in that box in it's applicable.

Thanks to both of you.

 

[Bob Phillips]

what do you want to happen when there is an error?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[michaelberrier]

Bob.
Thanks.
I need to call the referenced macro, Module22.Look_Here1 just like it
does when the value is not true.

Basically, I need it to display the message box if the value is True,
and run that macro(Look_Here1) if that value is anything else.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub 'only one cell at a time

'exit unless it Is D7 that changed
If Intersect(Target, Me.Range("$D$7")) Is Nothing Then Exit Sub

If IsEmpty(Target.Value) Then Exit Sub
If Not IsError(Range("D4").Value) Then
If Range("D4") = True Then
MsgBox "Check Driver's License Expiration Date", _
48, "Exprired Driver 's License """
Range("D7").Select
Else
Call Module22.Look_Here1
'the macro To Call when D7 changes
End If
Else
Call Module22.Look_Here1
End If

End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[michaelberrier]

Bob,
Absolutely perfect. Thanks for the help.