How do I only allow a whole number between 2014-2050 in an input box?

[gkisystems]

I have some VBA code that asks the user to input the year of the report in the format of YYYY. How do I restrict the code so that the user can only input a year between 2014-2050? For example, I do not want the user to type in 15 for the year 2015.

Here is the code I have so far:

'Ask user to provide the current Year and apply it to the data automatically.
    Dim myYear As String
    myYear = InputBox("Please key in the report year (''YYYY'' format) like 2015 for example:")
    Range("B2:B" & Lastrow).Value = myYear

I also need to figure out how to do code similar to the above for the user to figure out the QTR (either 1, 2, 3, or 4). One way of doing it might be to give the user a list with radio buttons next to Q1, Q2, Q3, Q4 for example. However, I'm not sure how to do that. Any help is appreciated.

 

[Domenic]

Try something like this...

    [COLOR=darkblue]Dim[/COLOR] myYear [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] bOk [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    bOk = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Do[/COLOR]
        myYear = InputBox("Please key in a report year between 2014 and" & vbCrLf & "2050 ('YYYY' format) like 2015 for example:")
        [COLOR=darkblue]If[/COLOR] Len(myYear) = 0 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]If[/COLOR] Len(myYear) = 4 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] IsNumeric(myYear) [COLOR=darkblue]Then[/COLOR]
                myYear = [COLOR=darkblue]CLng[/COLOR](myYear)
                [COLOR=darkblue]If[/COLOR] myYear >= 2014 And myYear <= 2050 [COLOR=darkblue]Then[/COLOR]
                    bOk = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] bOk

Hope this helps!

 

[Rick Rothstein]

Try something like this...

    [COLOR=darkblue]Dim[/COLOR] myYear [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] bOk [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    bOk = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Do[/COLOR]
        myYear = InputBox("Please key in a report year between 2014 and" & vbCrLf & "2050 ('YYYY' format) like 2015 for example:")
        [COLOR=darkblue]If[/COLOR] Len(myYear) = 0 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]If[/COLOR] Len(myYear) = 4 [COLOR=darkblue]Then[/COLOR]
            [COLOR=red][B]If IsNumeric(myYear) Then[/B][/COLOR]
                myYear = [COLOR=darkblue]CLng[/COLOR](myYear)
                [COLOR=darkblue]If[/COLOR] myYear >= 2014 And myYear <= 2050 [COLOR=darkblue]Then[/COLOR]
                    bOk = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] bOk

Hope this helps!

I would change the highlighted line to this...

If myYear Like "####" Then

Otherwise the user could crash the code by typing in something like 2E99. By the way, if you use my suggestion, you can eliminate the test for Len(myYear)=4 as the Like operator comparison will handle that automatically.

 

[Domenic]

Thanks Rick, great suggestion!