Help with InputBox and MsgBox

[Preschool Mike]

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

 

[ozgrid.com]

Sub NewStartDate()
Dim Prompt As String, Title As String
Dim StartDate As String

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

If StartDate = vbNullString Then Exit Sub

If Not IsDate(StartDate) Then
MsgBox "Date is not valid"
Run "NewStartDate"
End If
End Sub

 

[Dave Peterson]

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]

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
--
Mike Mast
Special Education Preschool Teacher

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]

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 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

--
Mike Mast
Special Education Preschool Teacher

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

 

[Preschool Mike]

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

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.

 

[Dave Peterson]

If you're asking for a date and they leave the inputbox empty, then they didn't
enter a date.

Either they should hit the cancel button or enter a date.

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.

 

[Dave Peterson]

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

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

 

[Preschool Mike]

I see, thanks for taking the time to help and explain. You've been a big help.

 

[Dave Peterson]

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