Day and Date Validation

[ServiceChris]

Having downloaded some really useful VB stuff from this forum over th
past few days, I thought I would ask to see if anyone can help with m
current problem.

I am trying to design a form for entering data by a large number o
people. One of the colums has the 'week ending' in it, which I woul
like to be a vaild date, and only a friday. I can get XL to check tha
a valid date has been entered, but does anyone have any suggestions ho
to check that the date is a Friday, and reply with an error messag
until a valid date is entered?

Thanks in advance.

Chri

 

[JulieD]

Hi Chris

there's a weekday function that returns the day of the week for a date
e.g.

Sub checkfri()

Dim i As Integer

Range("A1").Select
i = Weekday(Range("A1"))
If i <> 6 Then
MsgBox "Not Friday"
End If

End Sub

Hope this helps

Cheers
JulieD

 

[Harald Staff]

Hi Chris

Hopefully even better; suggesting the nearest friday and offering to correct
it ? Rightclick sheet tab, choose View code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim D As Date, DFri As Date
If Target.Address = "$B$3" Then
If Target.Value = "" Then Exit Sub
If IsDate(Target.Value) Then
D = Target.Value
Select Case Weekday(D, vbFriday)
Case 2 To 4
DFri = D - Weekday(D, vbFriday) + 1
Case 5 To 7
DFri = D - Weekday(D, vbFriday) + 8
Case Else
Exit Sub
End Select
If MsgBox(D & " is not a friday. Change to " & _
DFri & " ?", vbYesNo + vbQuestion) = vbYes Then
Target.Value = DFri
Else
Target.Value = ""
End If
End If
End If
End Sub

As is it does this in cell B3 only. Edit that line to fit your needs. Remove
the msgbox thing to make it autocorrect without questions.

 

[ServiceChris]

Thanks to both of you for those very quick replies. They both worked
but I do like the auto correction.

Only another 5 more fields for me to validate now, so watch out fo
more posts.

Cheers, Chri

 

[Harald Staff]

Only another 5 more fields for me to validate now, so watch out for
more posts.

Please do. Thanks for the feedback.

Best wishes Harald
Followup to newsgroup only please