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 _
Before:=.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 _
Before:=.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 said:
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 Before:=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