VBA Input Box Data Restrictions

[Rowland Hamilton]

Folks:

My code in progress but is there a better way to force the user to input the correct month?:

[/COLOR]
[COLOR=black]Sub INPUT_NUMBER_FROM_1_THRU_12()[/COLOR]
[COLOR=black]Dim myNum As Long[/COLOR]
[COLOR=black]myNum = Application.InputBox("Enter Month # from 1-12", "CURRENT MONTH")[/COLOR]
[COLOR=black] [/COLOR]
[COLOR=black]If myNum < 1 Or myNum > 12 Then[/COLOR]
[COLOR=black]   myNum = Application.InputBox("You Must Enter Month # from 1-12", "CURRENT MONTH")[/COLOR]
[COLOR=black]     If myNum < 1 Or myNum > 12 Then[/COLOR]
[COLOR=black]       myNum = 1[/COLOR]
[COLOR=black]       MsgBox "INCORRECT INPUT SO DEFAULT TO  " & myNum[/COLOR]
[COLOR=black]       GoTo TakeItHome[/COLOR]
[COLOR=black]     Else[/COLOR]
[COLOR=black]       GoTo NoProblem[/COLOR]
[COLOR=black]     End If[/COLOR]
[COLOR=black]Else[/COLOR]
[COLOR=black]   GoTo NoProblem[/COLOR]
[COLOR=black] [/COLOR]
[COLOR=black]NoProblem:[/COLOR]
[COLOR=black]   MsgBox "You typed  " & myNum & ",  That is within the acceptable range"[/COLOR]
[COLOR=black]End If[/COLOR]
[COLOR=black] [/COLOR]
[COLOR=black]TakeItHome:[/COLOR]
[COLOR=black]'Application.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)[/COLOR]
[COLOR=black]'Application.InputBox ("Enter Month # from 1-12", "CURRENT MONTH", "12", Left, Top, HelpFile, HelpContextId, Type)[/COLOR]
[COLOR=black]End Sub[/COLOR]
[COLOR=black]

I may also want to force them to pick a valid month or they can't go on.

 

[mikerickson]

Perhaps something like this

Do
    uiMonth = Application.InputBox("Input the month number (1-12)", type:=2)
    If uiMonth = 0 Then Exit Sub: Rem cancel pressed
    uiMonth = Int(uiMonth)
Loop Until (0 < uiMonth And uiMonth < 13)

 

[Jaafar Tribak]

Building on Mike's suggestion but using the VBA InputBox function :

Do
    uiMonth = InputBox("Input the month number (1-12)")
    If StrPtr(uiMonth) = 0 Then Exit Sub: Rem cancel pressed
Loop Until (0 < uiMonth And uiMonth < 13)

MsgBox "You enetred month: " & uiMonth

Edit : It also prevents erroring out if the user enters a string.

 

[Rowland Hamilton]

Jaafar/Mike: It looks pretty awesome, I'll have to try it. Thanks - Rowland.

 

[mikerickson]

Jafar, in the InputBox method, specifying the type causes Excel to validate the entry as to type.
My mistake was that it should be type:=1.

 

[Jaafar Tribak]

Jafar, in the InputBox method, specifying the type causes Excel to validate the entry as to type.
My mistake was that it should be type:=1.

I too missed the type arg. I am not used to working with InputBox Method. I find the VBA built-in InputBox function more freindly unless a Range is required.

Thanks.

 

[Rick Rothstein]

My code in progress but is there a better way to force the user to input the correct month?:

[COLOR=black]myNum = Application.InputBox("Enter Month # from 1-12", "[COLOR=darkred]CURRENT MONTH[/COLOR]")[/COLOR]
[/QUOTE]
What exactly is the "correct month"? The title for the InputBox seems to indicate you are asking the user to input the [COLOR=darkred]CURRENT MONTH[/COLOR] number... is that what you expect them to do? IF SO, you don't need to take input from them at all... VB can get the number for you directly.
 
[CODE]myNum = Month(Now)

 

[mikerickson]

Do
    uiMonth = Application.InputBox("Input the month number (1-12)", [COLOR="Red"]default:= Month(Date())[/COLOR], type:=1)
    If uiMonth = 0 Then Exit Sub: Rem cancel pressed
    uiMonth = Int(uiMonth)
Loop Until (0 < uiMonth And uiMonth < 13)

 

[Rowland Hamilton]

Folks: Thanks for the question and the help. I need the last completed month, corresponding to the data in the report, not the current month, so I changed the title to "Report Month" but I'll change it to "LAST COMPLETE MONTH" so its extra clear. - Thanks, Rowland

 

[Rowland Hamilton]

Probably need default as previous month number instead of current. Way better than "1" like I was trying to use before. - Thanks

Sub Alternate_Mo_Input_method()
   ' it works, thanks.
Do
    uiMonth = Application.InputBox("Input the month number (1-12)", "LAST COMPLETED MONTH", Default:=Month(Date) - 1, Type:=1)
    If uiMonth = 0 Then Exit Sub: Rem cancel pressed
    uiMonth = Int(uiMonth)
Loop Until (0 < uiMonth And uiMonth < 13)
'MsgBox "You entered month: " & uiMonth
 
End Sub