vba inputbox maintenance on cancel

Question

Im looking for a way to have inputbox pass only when I enter integer 1-12. Any string in it, double value, empty or ESCAPE (cancel) button will throw Exit Sub

I know there is a lot of exmaples online, I tried them all! but always on ESCAPE or other occasion it get error.

so far I have this, but it gets error on CANCEL:

dMonth = InputBox("Which month to count?", "Choose month", Format(Date, "m") - 1)
If (Not (Int(dMonth) >= 0 And Int(dMonth) <= 12)) Or StrPtr(dMonth) = 0 Or StrPtr(dMonth) = 698279968 Or dMonth = "" Then Exit Sub

Any advice or ideas?

Thank you

Assuming dMonth is a String, since that's what InputBox returns, Int is going to have issues with a null string. Use Val instead. — Brian M Stafford
initialy i used integer, but it was too many issues, so i tried string as all google sggests. please give code example so if it will be fully working i can flag is as correct answer — Jiri Zaloudek

Brian M Stafford Brian M Stafford · Accepted Answer · 2020-04-16T18:44:14

Try this:

Private Sub Command1_Click()
   Dim sMonth As String
   Dim dMonth As Double

   sMonth = InputBox("Which month to count?", "Choose month", Format(Date, "m") - 1)
   dMonth = Val(sMonth)                         'convert user input to numeric
   If dMonth <> Fix(dMonth) Then Exit Sub       'check for an integer
   If dMonth < 1 Or dMonth > 12 Then Exit Sub   'check for required range

   MsgBox dMonth
End Sub

vba inputbox maintenance on cancel

1 Answers