The cells is not deleting the value

[Taurus]

Hello,

I have problem in deleting the specific cell that has date format. The
cell has date format set in the spreadsheet. For example - 28-Apr-2005
Basically, I would like to delete the cell when user enter the wrong
value. For example: 666
But for some reason it doesn't allow me to delete the cells. And the
cell is not password protected and user allow to edit the field.
I used the syntax - selection.clearcontents
But it doesn't work. Did anyone has the same issue?

I build this function in a module. In the spreadsheet, when user enter
the value that is not the date format it should prompt the error
message and clear the cell contents.

Any Idea? Thank you in advanced

Public Function ValidateDate(vDate As Variant) As Boolean
On Error Resume Next

Do
If vDate = "" Then
ValidateDate = False
Exit Do
End If

If VBA.Year(vDate) <= 1980 Then
MsgBox "Please Enter the Test Date in yyyy-mm-dd format"
Sheet5.Activate
Range("D15").Select
Selection.ClearContents
ValidateDate = False
Exit Do
End If

If VBA.IsDate(vDate) Then
ValidateDate = True
Exit Do
Else
ValidateDate = False
MsgBox "Please Enter the Test Date in yyyy-mm-dd format"

Sheet5.Activate
Sheet5.Range("D15").Select
Selection.ClearContents
Exit Do
End If
Loop
End Function

 

[JE McGimpsey]

I have problem in deleting the specific cell that has date format. The
cell has date format set in the spreadsheet. For example - 28-Apr-2005
Basically, I would like to delete the cell when user enter the wrong
value. For example: 666
But for some reason it doesn't allow me to delete the cells. And the
cell is not password protected and user allow to edit the field.
I used the syntax - selection.clearcontents
But it doesn't work. Did anyone has the same issue?

I build this function in a module. In the spreadsheet, when user enter
the value that is not the date format it should prompt the error
message and clear the cell contents.

First -

it's nearly impossible to force a user to enter dates in the format you
want - the entry is analyzed by XL's parser before any macro gets a
chance to look at it.

It's also *usually* irrelevant, as long as the cell is not formatted as
text. dates are stored as integer offsets to a base date, so whether the
date is entered as

2006-05-01

or

01-05-2006

XL will still store the value as 37376 (1904 date system). What is
displayed depends on the cell format, not how the entry is parsed.
Unless, of course, the cell is formatted as Text, in which case the
entry isn't parsed at all, but rather is displayed as text.

Second -

Functions called from the worksheet CANNOT change the value in another
cell. Functions, even User Defined Functions, RETURN values to their
calling cells - they can't affect other cells, they can't change
selections, and they can't affect things such as a cell's formatting.

One way to do what you want is
a) Set up Data/Validation to accept dates within the desired range,
and

b) Format the cell the way you want the date to be displayed.

An alternative would be to use an Event Macro (which can change cell
values). For instance, if the entry cell is D15, put something like this
in the worksheet code module (CTRL-click the worksheet tab and choose
View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const dtEARLIESTDATE As Date = #1/1/80#
Const sMsg As String = "Please enter a valid date"
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "D15" Then
If IsDate(.Value) Then
If .Value <= dtEARLIESTDATE Then
.ClearContents
MsgBox sMsg
.Activate
Else
.NumberFormat = "yyyy-mm-dd"
End If
End If
End If
End With
End Sub

 

[Taurus]

Thank you for the explanation ... it's a very good tip to start on ))