Looping an Inputbox.

[Kayslover]

Hi All,

I need to loop an Input box until the User answers Yes

I ask for a year, and once the input is received I ask the year that has been entered is correct.

If the answer is Yes, then carry on with code, if the answer is No, then I want it to loop back to where is asks for the year again.

The code I have is :-

Dim myYear As Integer
Dim Confirm As Integer
'Populate Cell R2 in Sheet named Forumula with the YYYY that will be entered by the User.

myYear = InputBox("Please enter the year (Format YYYY) that you want to create monthly Till Taking Sheets for and other associated files.", Title:=" Company Name")
Sheets("Formula").Select
Range("R2").Value = myYear
Confirm = MsgBox("The Year you want to create files for is " & myYear & " Is that correct?", vbQuestion + vbYesNo + vbDefaultButton2, Title:=" Company Name")
If Confirm = vbYes Then
     MsgBox "The Year you want to create files for is " & myYear & " Is that correct?", vbQuestion + vbYesNo + vbDefaultButton2, Title:=" Company Name "
Else
[COLOR=rgb(0, 0, 0)][B]     Don’t know how to get back to the above InputBox[/B][/COLOR]
End If

 

[Fluff]

How about

Dim myYear As Variant
Dim Confirm As String
'Populate Cell R2 in Sheet named Forumula with the YYYY that will be entered by the User.

Do
   myYear = InputBox("Please enter the year (Format YYYY) that you want to create monthly Till Taking Sheets for and other associated files.", Title:=" Company Name")
   If myYear = "" Then Exit Sub
   Confirm = MsgBox("The Year you want to create files for is " & myYear & " Is that correct?", vbQuestion + vbYesNo + vbDefaultButton2, Title:=" Company Name")
Loop Until Confirm = vbYes
Sheets("Formula").Select
Range("R2").Value = myYear

 

[Kayslover]

Fluff,

Will try it out tomorrow and let you know (not doubting what you have provided).

 

[Fluff]

Ok,

 

[Kayslover]

Fluff,

I have tried out your revised code and as expected, it works like a charm.

What would I do if I wanted to ensure that the year entered is either the current year or in the future?

If the Year entered is in the past, or the User has just selected OK in the InputBox, then display the message “Year in the past or Invalid Year entered”

Loop until a valid year has been entered.

 

[Fluff]

You need to give the user a chance to escape, otherwise they are stuck in a permanent loop with no way out.
To check for a valid date use

Sub Kayslover()
Dim myYear As Variant
Dim Confirm As String
Dim AllOk As Boolean
'Populate Cell R2 in Sheet named Forumula with the YYYY that will be entered by the User.

Do Until AllOk
   myYear = InputBox("Please enter the year (Format YYYY) that you want to create monthly Till Taking Sheets for and other associated files.", Title:=" Company Name")
   If myYear = "" Then Exit Sub
   If Val(myYear) >= Year(Date) Then
      Confirm = MsgBox("The Year you want to create files for is " & myYear & " Is that correct?", vbQuestion + vbYesNo + vbDefaultButton2, Title:=" Company Name")
      If Confirm = vbYes Then AllOk = True
   Else
      MsgBox "please enter a vaild date"
   End If
Loop
Sheets("Formula").Select
Range("R2").Value = myYear
End Sub

 

[Kayslover]

Fluff,

Fantastic, works great.?

Once again I appreciated your assistance.

 

[Fluff]

Glad to help & thanks for the feedback.