Looping an Inputbox.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
128
Office Version
  1. 2013
Platform
  1. Windows
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 :-

VBA Code:
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,796
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Board Regular
Joined
Sep 22, 2020
Messages
128
Office Version
  1. 2013
Platform
  1. Windows
Fluff,

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,796
Office Version
  1. 365
Platform
  1. Windows

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
128
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,796
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
 
Solution

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
128
Office Version
  1. 2013
Platform
  1. Windows
Fluff,

Fantastic, works great.👍

Once again I appreciated your assistance.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,796
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,291
Messages
5,595,274
Members
413,984
Latest member
stikpet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top