Handling errors

[Guest]

Hi,

I want to write a macro that should check if the value in Cell C3 is in
valid date format. If it is not then, it should clear the contents and alert
the user to enter the value only in Date format.

Also, the macro should check if the cell is empty or not. Incase it is empty
then it should ask the user to enter the date of scheduled audit.

However, my problem is that On error code is being executed, even when the
cell is empty !


Here is the piece of code:


On Error Resume Next ' should be executed only when the cell value overflows
Range("c3").ClearContents
msg = MsgBox(" Pls enter a valid date", vbOKOnly, "Enter the Date")


If IsEmpty(Range("C3").Value) Then ' should be executed when the cell
is empty
Ans = MsgBox(" Pls enter the date of the scheduled audit", vbOKCancel,
"Enter the Date")

End

thanks

 

[NickHK]

Why not give more help to the user, either with a calendar control and/or a
userform, or provide 3 cells for day, month, year, maybe using Validation.
Otherwise it may not be clear to the user what you consider a valid date
format; dd/mm/yy, mm/dd/yy, yyyy-mm-dd, etc.

NickHK

 

[Guest]

Hi Shilps -

The replacement code below will check cell C3 for empty or an invalid date
string/value and prompt the user under either condition. I opted for the
replacement code because your 'On Error' approach didn't seem appropriate for
checking a cell's contents. Also, if you consider some of the options
described by NickHK to enhance your user's experience, it wouldn't apply.

Sub shilps()
If Range("C3") = "" Or Not (IsDate(Range("C3"))) Then
Range("c3").ClearContents
Range("C3") = InputBox _
("Pls enter a valid date", "Enter the Date")
End If

If Not (IsDate(Range("C3"))) Then
MsgBox "Invalid date entered."
Range("C3").ClearContents
Else
Range("C3").NumberFormat = "m/d/yy;@"
End If
End Sub