VBA - Ask for date & check if it falls on a Monday

[Phil Smith]

Hello everyone.

I would like to request some help for the code for the following problem:

Request a date to be entered in dd/mm/yy format. (Input Box?)

If date does NOT comply with dd/mm/yy format, request date again.

If date is earlier than today, request date again.

If date is on a Monday then Cells(6,2) = date entered

If date is NOT on a Monday then Cells(6,2)= first Monday after date entered

Hope this is fairly simple!

Regards,

Phil

 

[Norie]

Phil

Couldn't you just check that a valid date has been entered rather than checking that a valid date with a particular format has been entered?

You could easily do that using IsDate and it would give the user a little freedom in what the input.

And once a valid date has been entered you can format it as you want/need.

PS I'll need to have a think about the Monday thing.

It's probably straightforward but I'm watching the football so don't have much time for testing etc.

 

[Domski]

Does this do what you're after?

Sub test()
Dim my_date As Date
Do While my_date < Date Or Not IsDate(my_date)
my_date = InputBox("Enter a date")
Loop
If Weekday(my_date) = 2 Then
Cells(6, 2) = my_date
Else
Cells(6, 2) = my_date - Weekday(my_date, 2) + 8
End If
End Sub

Dom

 

[Domski]

It's probably straightforward but I'm watching the football so don't have much time for testing etc.<!-- / message --><!-- sig -->

Bugger, the football I forgot.........

 

[VoG]

Or

Sub test()
Dim Response As String, MyDate As Date
Dim iDay As Integer
Response = Application.InputBox("Enter date")
If IsDate(Response) Then
    MyDate = DateValue(Response)
Else
    MsgBox "Invalid date"
    Exit Sub
End If
If Weekday(MyDate) <> 2 Then MyDate = MyDate - (MyDate - 2) Mod 7 + 7
Cells(6, 2).Value = Format(MyDate, "dd/mm/yy")
End Sub

 

[Phil Smith]

Thanks for your help guys!

My macro is coming along nicely.

Just experimenting now and come up with another problem which I shall post under the heading "Problems with dates"

Cheers!