1

I am currently designing a macro that will require 3 inputs from a user, I want to know if I could somehow require the numeric inputs to require a 5 digit serial number. And restrict anything that is more or less then five digits. This is my code so far: 

Sub MRN_numbers()

  Dim s, e As Integer
  Dim m As String

  s = InputBox("Please enter the starting 5 digit MRN number")
  m = InputBox("please enter the material type")
     If Not m = "ebara" Or m = "mirra" Or m = "300mm" Then
     MsgBox ("Please enter valid material name!")
     m = InputBox("Please enter the material type")
     End If

  e = InputBox("pleae enter the ending 5 digit MRN number")

Range("D1").Activate
Range("D65536").End(xlUp).Offset(1, 0).Activate

For i = s To e
If m = "ebara" Or m = "mirra" Or m = "300mm" Then

    If m = "ebara" Then
        For l = 1 To 5
            ActiveCell.Value = i & "-" & l
            ActiveCell.Offset(1, 0).Activate
            ActiveCell.Offset(-1, -1).Value = "Ebara"
        Next l

    End If

    If m = "mirra" Then
        For r = 1 To 6

            ActiveCell.Value = i & "-" & r
            ActiveCell.Offset(1, 0).Activate
            ActiveCell.Offset(-1, -1).Value = "Mirra"
        Next r
    End If

    If m = "300mm" Then
        For y = 1 To 4
            ActiveCell.Value = i & "-" & y
            ActiveCell.Offset(1, 0).Activate
            ActiveCell.Offset(-1, -1).Value = "300mm"
        Next y
    End If


End If


  Range("D65536").End(xlUp).Offset(1, 0).Activate






Next i

End Sub

I am not sure where to start on this, I watched videos but didn't find anything that helped, I am going to keep looking and trying different ways in the mean time, but any help on this would be appreciated.

Community
  • 1
  • 1
Charlie West
  • 61
  • 2
  • 12
  • 3
    `InputBox` was really meant for development/debugging, not for collecting actual user input. It's much easier (and user-friendly) to implement proper validation with a custom `UserForm`. – Mathieu Guindon Oct 26 '16 at 18:20

1 Answers1

5

Wrap each entry in a Do Loop:

Do
    s = InputBox("Please enter the starting 5 digit MRN number")
    If Not (IsNumeric(s) And Len(s) = 5) Then MsgBox s & " is not a 5 digit number"
Loop Until IsNumeric(s) And Len(s) = 5

So for e

Do
    e = InputBox("Please enter the ending 5 digit MRN number")
    If Not (IsNumeric(e) And Len(e) = 5) Then MsgBox e & " is not a 5 digit number"
Loop Until IsNumeric(e) And Len(e) = 5
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I have one problem this loop doesn't work for my second numeric input, I don't know what I have done wrong. This is your code adapted to the variable e: `Do e = InputBox("Please enter the starting 5 digit MRN number") If Not (IsNumeric(e) And Len(e) = 5) Then MsgBox e & " is not a 5 digit number" Loop Until IsNumeric(e) And Len(e) = 5` – Charlie West Oct 26 '16 at 18:42
  • @CharlieWest the code works for me. see edit for a tested version. – Scott Craner Oct 26 '16 at 18:47
  • I'm still getting the same problem, for some reason it thinks a five digit number has a len = 2. Not sure why. – Charlie West Oct 26 '16 at 19:14
  • Not sure what is happening as I have tested the code and am not having an issue. – Scott Craner Oct 26 '16 at 19:20
  • well thanks for the help I'll keep messing with it hopefully I can figure out why its not working. – Charlie West Oct 26 '16 at 19:22
  • I figured out my problem I had my s and e variables assigned as integers, when I removed the `Dim s, e As integers` piece of code it started to work. – Charlie West Oct 26 '16 at 19:36
  • Yes s was being declared as a variant and e as an integer use this instead `Dim s as long, e as long` – Scott Craner Oct 26 '16 at 19:37
  • Remember to marks as correct by clicking the check mark by the answer. – Scott Craner Oct 26 '16 at 19:38