Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
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) "michaelberrier" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
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" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
what do you want to happen when there is an error?
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "michaelberrier" wrote in message oups.com... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
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" wrote in message ups.com... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error handling in macro
Bob,
Absolutely perfect. Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |