PDA

View Full Version : How to loop a date



dnorris404
07-09-2010, 05:18 PM
I am trying to do like a test input "survey" and i made it so that if you dont enter anything than it will keep looping until you do...well this only work with the "name" input not the "birthdate" input(you'll see what those are later). When I loop the "birthdate" input, it gives me the "run time error '13'..."Type Mismatch"...and it doesnt even show the birthdate input box...just goes from the name input box to the error... heres my code:
Sub Macro1()
Dim name As String
Dim birthdate As Date
Do While name = ""
name = InputBox("What is Your Name?", "Name")
Loop
Do While birthdate = ""
birthdate = InputBox("When is Your Birthday?", "Birthday")
Loop
Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("A2").Select
ActiveCell.FormulaR1C1 = name
Range("B1").Select
ActiveCell.FormulaR1C1 = "Birthday"
Range("B2").Select
ActiveCell.FormulaR1C1 = birthdate
Range("A1").Select
ActiveCell.EntireColumn.AutoFit
Range("A1").EntireColumn.AutoFit
MsgBox "You Have Completed the Survey", vbOKOnly, "Completion"
End Sub
and another question...i heard there was a way to make a message box appear when there is an error so that the error thing doesnt appaer...just the message box but i dont know how to do it.

AlphaFrog
07-09-2010, 09:36 PM
Try something like this...

Sub Macro1()
Dim name As String
Dim birthdate As Variant

On Error GoTo ErrHandler

Do
name = Application.InputBox("What is Your Name?", "Name", Type:=2)
If name = "False" Then Exit Sub 'User canceled
Loop While name = ""

Do
birthdate = Application.InputBox("When is Your Birthday?", "Birthday", Type:=2)
If birthdate = "False" Then Exit Sub 'User canceled
Loop While Not IsDate(birthdate)

Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("A2").Select
ActiveCell.FormulaR1C1 = name
Range("B1").Select
ActiveCell.FormulaR1C1 = "Birthday"
Range("B2").Select
ActiveCell.FormulaR1C1 = birthdate
Range("A1").Select
ActiveCell.EntireColumn.AutoFit
Range("A1").EntireColumn.AutoFit
MsgBox "You Have Completed the Survey", vbOKOnly, "Completion"

Exit Sub

ErrHandler:
MsgBox "There was an error"

End Sub

AlphaFrog
07-09-2010, 09:36 PM
Deleted. (It posted twice for some reason).

mdmackillop
07-11-2010, 02:17 AM
If you want multiple entries, you need something like this. Also, try to avoid Selecting


Range("A1") = "Name"
Range("B1") = "Birthday"
Cells(Rows.Count, 1).End(xlUp)(2) = name
Cells(Rows.Count, 2).End(xlUp)(2) = birthdate
Range("A:B").EntireColumn.AutoFit
MsgBox "You Have Completed the Survey", vbOKOnly, "Completion"