How do I include a date input box in a macro?

[Project Mgr]

I want to record a macro which will show a input box into which I can enter
any date and then continue. I can't find the information in help how to do
this.

 

[Bob Phillips]

Look at Inputbox in help. You will need to validate that the input really is
a date.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tim879]

put a sample date in an unused cell of your spreadsheet. record your
macro....

after recording the macro, insert the following lines near the
beginning of your macro


dim usr_date as string 'you can call this whatever you want
usr_date = inputbox("Please enter a date: ")


find where you referenced the unused cell from above (for example
cell B1) and modify the code so that you now reference the usr_date
variable instead

 

[Gizmo63]

Taking Tim879's response a little further try this:

Dim usr_Date As Date
Dim tempResponse As Variant

ShowDateBox:
tempResponse = InputBox("Please enter date.", "Date Required",
Format(Now(), "dd-mmm-yyyy"))

If tempResponse = "" Then Exit Sub
If IsDate(tempResponse) = False Then GoTo ShowDateBox
usr_Date = tempResponse

usr_Date is a validated response that can be used elsewhere.
tempResponse is a variant to allow for any entry and holds the answer to the
input box, which is then tested for blank or valid date.
A 'Cancel', blank or Null entry stops execution.
If anything other than a date is entered the user is forced back to the
input box.
The above input box also has a default entry of today's date, if you don't
need that remove the 'format' bit to leave
'tempResponse = InputBox("Please enter date.", "Date Required")


HTH
Giz

 

[Pete_UK]

Here's one I prepared earlier ... (as they say):

Dim check_date As Integer
Dim my_date As String

'some other code

Do Until check_date = vbYes
my_date = ""
Do Until IsDate(my_date)
my_date = InputBox("Please Input date for this download")
Loop
download_date = DateValue(my_date)
check_date = MsgBox("Download Date = " & download_date _
& " - Are you sure?", vbYesNo)
Loop

'rest of code

This gives two levels of validation, as it then used the date to date-
stamp files, so it was important to get the right one.

Hope this helps.

Pete

 

[Project Mgr]

THANK YOU, TIM 879. IT SEEMS THAT IT SHOULD WORK, BUT THE DATE DOES NOT SHOW
UP IN THE CELL. HERE IS THE CODE FOR MY ENTIRE MACRO. WHERE IS THE PROBLEM?

Sub DateNeeded()
'
' DateNeeded Macro
' Macro recorded 6/19/2008 by dewey
'
' Keyboard Shortcut: Ctrl+d
'
Selection.NumberFormat = "ddd, mmm d"
Dim usr_date As String 'date
usr_date = InputBox("Please enter date:")
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

 

[Jim Thomlinson]

Give this a whirl...

Sub DateNeeded()
'
' DateNeeded Macro
' Macro recorded 6/19/2008 by dewey
'
' Keyboard Shortcut: Ctrl+d
'
with Selection
.value = getdate
.NumberFormat = "ddd, mmm d"
.Interior.ColorIndex = 6
End With
End Sub

Public Function GetDate() As Date
Dim strDate As String
Dim blnValidDate As Boolean
Dim dte As Date
Dim strMsg As String

strMsg = "Please enter a date."
blnValidDate = False
Do While blnValidDate = False
strDate = InputBox(strMsg)
On Error Resume Next
GetDate = CDate(strDate)
On Error GoTo 0
If GetDate <> 0 Then blnValidDate = True
strMsg = "Invalid date. Please try again."
Loop
End Function