VBA Case statement

[Ken]

Hi Group, I had posted this message a short time ago, and because the
subject matched another set of messages it showed up as a reply to
another posting. My apologies!

Hi Group,
I don't know exactly what I'm doing with this VBA:

Select Case Cells(r, "K").Value
Case Is <> "m&/&d&/&yy"
MsgBox "You have entered an invalid date."


That does not work. What I'm "trying " to do is check a cell that
contains a date to see if it has extraneous characters causing an
invalid date. I'm trying to prevent usiers from mis-typing things
into
the cell such as *or ' or // or " for instance. I just can't wrap my
head around a proper way to set it up. I have another Select Case
that
checks to see if a cell contains a weekend date, and it works very
well until someone mis-types the aforementioned instances:


Select Case Weekday(Cells(r, "K").Value)
Case 1, 7


MsgBox "You have entered a weekend date." _
& vbLf & "Please enter the date for Friday, or the date
for Monday!"


Cells(r, "K").ClearContents
Case 2, 3, 4, 5, 6
Case Else


If anyone can point me in the right direction, the help will be most
greatly appreciated. Thank so much to the group!
Ken

 

[Chip Pearson]

You don't need a Select Case. The easiest way to see if a string of
text is a valid date is to use the DateValue function. This converts a
string to a date, if possible, or throws an error if the string is not
an valid data. For example,

Dim DV As Date
Dim S As String
S = "12/15/2009" ' valid date
S = "12'!15--2009" ' invalid date
On Error Resume Next
Err.Clear
DV = DateValue(S)
If Err.Number = 0 Then
MsgBox "Valid Date"
Else
MsgBox "Invalid Date"
End If

You could wrap this up into a function that can be called from any VBA
code or from a worksheet cell:

Function IsValidDate(S As String) As Boolean
Dim DV As Date
On Error Resume Next
Err.Clear
DV = DateValue(S)
IsValidDate = (Err.Number = 0)
End Function

You can call this function from other VBA with

Dim B As Boolean
S = "12/14/2009"
B = IsValidDate(S)
If B = True Then .....

or from a worksheet cell with
=IsValidDate(A1)




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ken]

Hi Chip,
I know you must get extremely frustrated with some of us just kinda'
hacking away sometimes, but for the last 2 hours I have tried to get
your VBA to work. I have inserted the Function in a module, created a
Sub:
Sub DateCheck()
Dim DV As Date
Dim S As String
S = "12/15/2009" ' valid date
S = "12'!15--2009" ' invalid date
On Error Resume Next
Err.Clear
DV = DateValue(S)
If Err.Number = 0 Then
MsgBox "Valid Date"
Else
MsgBox "Invalid Date"
End Sub

and called the sub from another macro. I never have gotten "Valid
Date", and I get "Invalid Date" on valid dates. Now I'm so confused
I'm just going to delete it all and start again. Could you please
explain to me again how to make this work and remember--I'm a Dummy!
If you wish not to, then I'll understand completely. Thanks for your
help!
Ken

 

[Rick Rothstein]

Unless I am missing something in this question, wouldn't this be a better
approach to use for your IsValidDate function?

Function IsValidDate(S As String) As Boolean
IsValidDate = IsDate(S)
End Function

 

[Ken]

Thanks Chip and Rick for your help! You got me thinking. I
accomplished what I was after with this:

Select Case IsDate(Cells(r, "K").Value)
Case Is <> Date
MsgBox "You have not entered a valid date. Please re-
enter."
Cells(r, "K").Select
Case Else

Select Case Weekday(Cells(r, "K").Value)
Case 1, 7

MsgBox "You have entered a weekend date." _
& vbLf & "Please enter the date for Friday, or the
date for Monday!"
Cells(r, "K").Select

End Select
End Select

Again, my thanks!
Ken