Left out some of my code
Option Explicit
Dim StartDate As Date
Dim MessageReminder As String
'Prompt for user to enter new date
Sub newStartDate()
Dim myPrompt As String
Dim myTitle As String
myPrompt = "What's the new start date?"
myTitle = "Start Date"
StartDate = Application.InputBox(Prompt:=myPrompt, Title:=myTitle, Type:=1)
If Year(StartDate) < Year(Date) Then
MsgBox "You entered the date incorrectly"
Exit Sub
End If
MsgBox Format(StartDate, "mmm-d-yy")
End Sub
'Run all the codes....and puts the dates in the correct cells
Sub EnterNewPayPeriod()
MessagePrompt
NewTimeSheet
newStartDate
Range("I4") = StartDate
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
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
"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
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
.