If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with InputBox and MsgBox
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 |
#2
|
|||
|
|||
Help with InputBox and MsgBox
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 -- Regards Dave Hawley www.ozgrid.com "Preschool Mike" wrote in message ... 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 |
#3
|
|||
|
|||
Help with InputBox and MsgBox
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 |
#4
|
|||
|
|||
Help with InputBox and MsgBox
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 . |
#5
|
|||
|
|||
Help with InputBox and MsgBox
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 . |
#6
|
|||
|
|||
Help with InputBox and MsgBox
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 . |
#7
|
|||
|
|||
Help with InputBox and MsgBox
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. Preschool Mike wrote: 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 . -- Dave Peterson |
#8
|
|||
|
|||
Help with InputBox and MsgBox
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 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 . -- Dave Peterson |
#9
|
|||
|
|||
Help with InputBox and MsgBox
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 |
#10
|
|||
|
|||
Help with InputBox and MsgBox
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 |
Thread Tools | |
Display Modes | |
|
|