One other problem happening. Using your code - If the user does not put
anything in the input box and clicks ok. I get the message "The formula you
typed contains and error" The post I have below explain what I'm trying to
accomplish.
--
Mike Mast
Special Education Preschool Teacher


"Dave Peterson" wrote:

You could use something like this:

Option Explicit
Sub NewStartDate2()
Dim myPrompt As String
Dim myTitle As String
Dim StartDate As Date

myPrompt = "What's the new pay period start date?"
myTitle = "Start Date"

StartDate = Application.InputBox(Prompt:=myPrompt, Title:=myTitle, Type:=1)

'some sanity check
If Year(StartDate) < Year(Date) Then
MsgBox "quitting"
Exit Sub
End If

MsgBox Format(StartDate, "mmm dd, yyyy")

End Sub

The type:=1 in the application.inputbox (which is different from VBA's Inputbox)
will force the user to type a number (and a date is a number to excel).

It makes validation a little simpler.

But I think that you may have a bigger problem.

If a user enters:
01/02/03
how can your program be sure what date the user meant.

You may want to consider creating a small userform with a calendar control. Ron
de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 different controls (year, month, day) to get that date.

Preschool Mike wrote:

How do I fix the below code so if a user does not enter anything in the input
box or clicks on the cancel button they get what I have in the MsgBox?

MsgBox ("You did not enter a new pay period start date" & vbCrLf & _
"click on the 'EnterNewPayPeriod' button and re-enter the new pay period")

Sub NewStartDate()
Dim Prompt, Title As String
Prompt = "What's the new pay period start date?"
Title = "Start Date"
StartDate = InputBox(Prompt, Title)

End Sub
--
Mike Mast
Special Education Preschool Teacher

--

Dave Peterson
.