Verification of Data Input..

[Cruizer67]

With the following code... I am using it to populate my spread sheets.. works well except for one issue i have.. It is possible that when the input box pops up.. you don't have to input any data Just press enter and it will work only fields that do get data are the date and time.. How can I make it so that if you don't input infomation (job #, Operator # , etc...) that it will force the user to put in info ? Or it won't input the data at all...?

any help would be good...

thanks


Private Sub CommandButton1_Click()
' Add a new job
Dim LastRow As Object
Set LastRow = Sheets("Sheet1").Range("A65536").End(xlUp)

With LastRow
.Offset(1, 0) = InputBox("Please enter a Job #", "Job #")
.Offset(1, 1) = InputBox("Please enter an Operator #", "Operator #")
.Offset(1, 2) = InputBox("Please enter a Part #", "Part #")
.Offset(1, 4) = Date
.Offset(1, 5) = Time
Unload Me

End With

End Sub

 

[HalfAce]

Hi Cruizer,
How about something like this:

Private Sub CommandButton1_Click()
'Add a new job
Dim LastRow As Object, a As String, b As String, c As String
Set LastRow = Sheets("Sheet1").Range("A65536").End(xlUp)

StartOver:
a = InputBox("Please enter a Job #", "Job #")
If Len(a) < 1 Then
    MsgBox ("Please enter a Job # to continue"), , "Job #"
        a = InputBox("Please enter a Job #", "Job #")
End If

b = InputBox("Please enter an Operator #", "Operator #")
If Len(b) < 1 Then
    MsgBox ("Please enter an Operator # to continue"), , "Operator #"
        b = InputBox("Please enter an Operator #", "Operator #")
End If

c = InputBox("Please enter a Part #", "Part #")
If Len(c) < 1 Then
    MsgBox ("Please enter a Part # to continue"), , "Part #"
        c = InputBox("Please enter a Part #", "Part #")
End If

With LastRow
    If Len(a) < 1 Then GoTo StartOver
      .Offset(1) = a
    If Len(b) < 1 Then GoTo StartOver
      .Offset(1, 1) = b
    If Len(c) < 1 Then GoTo StartOver
      .Offset(1, 2) = c
    .Offset(1, 4) = Date
    .Offset(1, 5) = Time
End With

Unload Me

End Sub

Hope it helps,
Dan

Edit:
Added some more tests at the bottom so it won't continue if there was no entry in the second chance too.

 

[Tazguy37]

Or:

Sub test()
Dim jobNumber

jobNumber = ""
Do
    jobNumber = InputBox("Please enter a Job #")
Loop Until (jobNumber <> "" And jobNumber <> False)

MsgBox (jobNumber)

End Sub

This is just a snippet to show how you could loop the InputBox until an entry is made. Also, this part:

And jobNumber <> False)

disables the cancel button, so use it with caution. I just thought I'd throw that in. Hope that helps!

 

[HalfAce]

Good catch Taz. I was editing for that condition when you beat me to it. If you had got here sooner it could've saved me a lot of typing!

Dan

 

[Cruizer67]

Thanks for the tips... with all the help i get from this forum.. it sure will make my life a work easier...

Thanks again....