Did you notice the pair of procedures in that previous message?

Preschool Mike wrote:

I see, thanks for taking the time to help and explain. You've been a big help.
--
Mike Mast
Special Education Preschool Teacher

"Dave Peterson" wrote:

Maybe I should have used an example like: 10/11/12

Is that 11-Oct-2012 or 10-nov-2012 or 12-nov-12 or what.

My point wasn't for that particular string of characters. It was that if the
user enters a date in an ambiguous format, you and your code would have no idea
what they really meant.

There are times when I think that variables and subroutines make good sense --
if you want to reuse the code (call it from different routines) or if you want
to use that variable in different spots.

For instance, if you wanted to use a common title for each of your
msgbox/inputboxes, you could assign the value to the variable (once) and use it
as often as you need it.

But there are other times where the code just gets more cluttered (in my
opinion) and more difficult to maintain.

I would do something like this:

Option Explicit
Sub EnterNewPayPeriod2()

Dim StartDate As Date
Dim NewWks As Worksheet

MsgBox "Never delete or move the 'Template' or 'Sheet2'!", _
vbCritical, "Important Reminder"

'try to get the date before doing anything else
StartDate = Application.InputBox(Prompt:="What's the new start date?", _
Title:="Start Date", Type:=1)

If Year(StartDate) < Year(Date) Then
MsgBox "You entered the date incorrectly"
Exit Sub
End If

With ActiveWorkbook
.Worksheets("Template").Copy _
Befo=.Sheets(2)
Set NewWks = ActiveSheet 'the one just created
End With

With NewWks
.Range("I4") = StartDate
.Name = Range("I4").Text
.Range("R4") = StartDate + 13
.Range("A6") = StartDate
.Range("A8") = StartDate + 1
.Range("A10") = StartDate + 2
.Range("A12") = StartDate + 3
.Range("A14") = StartDate + 4
.Range("A16") = StartDate + 5
.Range("A18") = StartDate + 6
.Range("A23") = StartDate + 7
.Range("A25") = StartDate + 8
.Range("A27") = StartDate + 9
.Range("A29") = StartDate + 10
.Range("A31") = StartDate + 11
.Range("A33") = StartDate + 12
.Range("A35") = StartDate + 13

.Range("AI4") = StartDate
.Range("AR4") = StartDate + 13
.Range("AA6") = StartDate
.Range("AA8") = StartDate + 1
.Range("AA10") = StartDate + 2
.Range("AA12") = StartDate + 3
.Range("AA14") = StartDate + 4
.Range("AA16") = StartDate + 5
.Range("AA18") = StartDate + 6
.Range("AA23") = StartDate + 7
.Range("AA25") = StartDate + 8
.Range("AA27") = StartDate + 9
.Range("AA29") = StartDate + 10
.Range("AA31") = StartDate + 11
.Range("AA33") = StartDate + 12
.Range("AA35") = StartDate + 13
End With

End Sub

I'm not quite sure what you're doing with the stuff in AI and AA (some kind of
history/tracker if something changes???).

But if it's for appearance only, I'd drop the code and replace it with formulas
like:
=a4
or
=if(a4="","",a4)

=====
ps. I like qualifying my ranges and worksheets. So I added some "with/end
with" lines. Depending on where this code is located, you may not notice a
difference. But it will never hurt to qualify those objects.

pps. Do the user's have to see that template worksheet? If no, you could hide
it (Format|sheet|hide in xl2003 menus). Then your code could unhide it, copy
it, and rehide it.

Option Explicit
Sub EnterNewPayPeriod2()

Dim StartDate As Date
'Dim MessageReminder As String
Dim NewWks As Worksheet

MsgBox "Never delete or move the 'Template' or 'Sheet2'!", _
vbCritical, "Important Reminder"

'try to get the date before doing anything else
StartDate = Application.InputBox(Prompt:="What's the new start date?", _
Title:="Start Date", Type:=1)

If Year(StartDate) < Year(Date) Then
MsgBox "You entered the date incorrectly"
Exit Sub
End If

Application.ScreenUpdating = False 'hide the flicker

With ActiveWorkbook
With .Worksheets("Template")
.Visible = xlSheetVisible
.Copy _
Befo=.Parent.Sheets(2)
Set NewWks = ActiveSheet 'the one just created
.Visible = xlSheetHidden
End With
End With

With NewWks
.Range("I4") = StartDate
'I'd be explicit here and not rely on the .text property
.Name = Format(StartDate, "yyyy-mm-dd")
.Range("R4") = StartDate + 13
.Range("A6") = StartDate
.Range("A8") = StartDate + 1
.Range("A10") = StartDate + 2
.Range("A12") = StartDate + 3
.Range("A14") = StartDate + 4
.Range("A16") = StartDate + 5
.Range("A18") = StartDate + 6
.Range("A23") = StartDate + 7
.Range("A25") = StartDate + 8
.Range("A27") = StartDate + 9
.Range("A29") = StartDate + 10
.Range("A31") = StartDate + 11
.Range("A33") = StartDate + 12
.Range("A35") = StartDate + 13

.Range("AI4") = StartDate
.Range("AR4") = StartDate + 13
.Range("AA6") = StartDate
.Range("AA8") = StartDate + 1
.Range("AA10") = StartDate + 2
.Range("AA12") = StartDate + 3
.Range("AA14") = StartDate + 4
.Range("AA16") = StartDate + 5
.Range("AA18") = StartDate + 6
.Range("AA23") = StartDate + 7
.Range("AA25") = StartDate + 8
.Range("AA27") = StartDate + 9
.Range("AA29") = StartDate + 10
.Range("AA31") = StartDate + 11
.Range("AA33") = StartDate + 12
.Range("AA35") = StartDate + 13
End With

Application.ScreenUpdating = True

End Sub



Preschool Mike wrote:

I'm just in the vb learning process, but why would the user enter that date
if all they need to do is enter the date for the current pay period. I could
see them entering it by accident, but doesn't the message prompt ask them to
re-enter it correctly? What I'm trying to accomplish is to use some vb code
to create a copy of my timesheet (i.e., Its a worksheet named template) that
enters the pay period start and end dates as well as all the day (e.g.,
dates) in the appropriate cells as well as renames the timesheet as the start
date. Everything I've done so far seems to work, I just needed some type of
message if the user didn't enter anything or clicked the cancle button for
the InputBox. Here's a look at my code. I know it's a bit wordy and there's
probably a better way, but with my limited experience this is the best I
could do.

Option Explicit
Dim StartDate As Date
Dim MessageReminder As String

Sub EnterNewPayPeriod()
MessagePrompt
NewTimeSheet
newStartDate
Range("I4") = StartDate 'Display the pay period start date
Range("R4") = StartDate + 13 'Display the pay period end date
Range("A6") = StartDate
Range("A8") = StartDate + 1
Range("A10") = StartDate + 2
Range("A12") = StartDate + 3
Range("A14") = StartDate + 4
Range("A16") = StartDate + 5
Range("A18") = StartDate + 6
Range("A23") = StartDate + 7
Range("A25") = StartDate + 8
Range("A27") = StartDate + 9
Range("A29") = StartDate + 10
Range("A31") = StartDate + 11
Range("A33") = StartDate + 12
Range("A35") = StartDate + 13

Range("AI4") = StartDate
Range("AR4") = StartDate + 13
Range("AA6") = StartDate
Range("AA8") = StartDate + 1
Range("AA10") = StartDate + 2
Range("AA12") = StartDate + 3
Range("AA14") = StartDate + 4
Range("AA16") = StartDate + 5
Range("AA18") = StartDate + 6
Range("AA23") = StartDate + 7
Range("AA25") = StartDate + 8
Range("AA27") = StartDate + 9
Range("AA29") = StartDate + 10
Range("AA31") = StartDate + 11
Range("AA33") = StartDate + 12
Range("AA35") = StartDate + 13
RenameTimeSheet
End Sub
'Makes a copy of the timesheet
Sub NewTimeSheet()
' NewTimeSheet Macro
Sheets("Template").Select
Sheets("Template").Copy Befo=Sheets(2)

End Sub
'Renames the time sheet
Sub RenameTimeSheet()
ActiveSheet.Name = Range("I4").Text
End Sub
'A reminder to never delete or remove the template or sheet2
Sub MessagePrompt()
MessageReminder = MsgBox("Never delete or move the 'Template' or 'Sheet2'!",
vbCritical, "Important Reminder")
End Sub
--
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

--

Dave Peterson