Choose from a list of the Thursdays in the last month (input box, macro)?

[MickFlanagen]

Hi,

I am currently using a very basic input box that allows the user to enter a date. I would like to restrict them to choosing only the Thursdays in the past month. Is this possible?

Here is my code so far:

Date = Application.InputBox("Please Enter The Appropriate Date", "Date", 0)
Range("A2").Value = Date

Thanks for the help.

MF

 

[Damon Ostrander]

Hi Mick,

I'm not sure if this is exactly what you were looking for, but this macro (with its helper function) will not accept a date value that is not within the past 30 days.

Sub Test()
   Dim ThursDate      As Date
   Do
      ThursDate = Application.InputBox("Please Enter The Appropriate Date", "Date", 0)
   Loop Until IsThursday(ThursDate, Date - 30)
   Range("A2").Value = ThursDate
End Sub

Function IsThursday(D As Date, Since As Date) As Boolean
   IsThursday = False
   If D < Since Or D > Date Then
      Exit Function
   ElseIf Weekday(D) = 5 Then
      IsThursday = True
   End If
End Function

It could be slightly modified to accept only dates in the past month (e.g., since it is now March, the acceptable date range would be Feb 1 to Feb 29, and would not include March 1). Note that I changed your variable "Date" to "ThursDate" to avoid a name conflict with the VBA Date function.

It is also possible to have an event procedure run when the cell gets selected that populates a validation list in the cell with the Thursdays in the last 30 days or the past month. It this is what you really wanted, let me know.

Damon

 

[hellebore08]

im thinking of this

Sub test()
Do
thursdate = InputBox("Please Enter The Appropriate Date", "Date", 0)
Loop Until Weekday(thursdate) = 5
Range("A2").Value = thursdate
End Sub