CODEWITHSUNDEEP
excelvba
Arianne Gale Cruz
Arianne Gale Cruz

Reputation: 11

Validate Input box entry between min and max number

I used InputBox for the "period" which is the month ("m").

When I try using it for the "year" ("YYYY") it does not function as expected.

My code for the "year" (same as in "period", only different values and variables):

Dim VYear as variant
Dim defY as variant

defY = Format(DateAdd("YYYY", 0, Now), "YYYY")
VYear = InputBox("Year covered","Year",defY)
If VYear > 2014 And VYear < defY Then
    Range("I1").Value = VYear
ElseIf VYear = "" Then
    Exit Sub
Else
    Do Until VYear > 2014 And VYear < defY
    MsgBox "Please enter a year not earlier than 2015 and not later than this year"
    VYear = InputBox("Year covered")
    Loop
End If

It does give me the default value of 2018. When I tried entering wrong values, it proceeds with the message in MsgBox as expected, but it would no longer accept ANY values, even the year "2018".

Cycle goes: MsgBox (Please enter....) then InputBox then MsgBox again.

I intentionally used As Variant so that even though users input letters, it won't give the error of "type mismatch".

Upvotes: 1

Views: 835

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

It should look like this …

Option Explicit

Public Sub AskForYear()
    Dim InputValue As Variant   'needs to be variant because input is FALSE if cancel button is pressed
    Dim DefaultYear As Integer

    DefaultYear = Year(Date)    'Get the year of the current date today

    Do
        InputValue = Application.InputBox(Prompt:="Please enter a Year between 2015 and " & DefaultYear & "." & vbCrLf & "Year covered:", Title:="Year", Default:=DefaultYear, Type:=1)
        If VarType(InputValue) = vbBoolean And InputValue = False Then Exit Sub  'cancel was pressed
    Loop While InputValue < 2015 Or InputValue > DefaultYear

    Range("I1").Value = InputValue 'write input value
End Sub

  • It uses a Do loop that is at least run once (note the criteria is in the Loop While part). It keeps asking for a date between 2015 and this year until the criteria is met. Then it will continue to write into the range.

  • Note that there is a cancel criteria to catch if the user pressed the cancel button. Then it exits the sub without writing into the range.

    This is just hypothetical for your specific case (asking for a year) but …
    For this criteria it is not sufficient to test If InputValue = False if you plan to accept 0 as number input. Therefore you need also to test for Booleantype:
    If VarType(InputValue) = vbBoolean
    This is because False is automatically cast to 0.

  • Note that I used Application.InputBox instead of InputBox. These are 2 completely different ones: 

    Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
'see the different parameters
InputBox(Prompt, Title, Default, XPos, YPos, HelpFile, Context) As String

    In Application.InputBox you can provide a Type parameter which I set to 1 which means it only accepts numbers (see Application.InputBox Method). With just InputBox you cannot do this.

  • I recommend to use meaningful variable names, which makes it much easier to read and maintain the code. Also only use Variant if really necessary. In this case it is, because the Application.InputBox can either return a Boolean (cancel button) or number (input).

  • Another recommendation is to always specify a worksheet for a Range like Worksheets("Sheet1").Range("I1") otherwise Excel guesses which worksheet you mean and it might easily fail.

Upvotes: 2

Related Questions