Verification of Data Input..

Cruizer67

New Member
Joined
Jan 17, 2005
Messages
31
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hi Cruizer,
How about something like this:
Code:
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Or:
Code:
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:
Code:
And jobNumber <> False)
disables the cancel button, so use it with caution. I just thought I'd throw that in. Hope that helps!
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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! :LOL:

Dan
 

Cruizer67

New Member
Joined
Jan 17, 2005
Messages
31
Thanks for the tips... with all the help i get from this forum.. it sure will make my life a work easier...

Thanks again....
 

Forum statistics

Threads
1,148,525
Messages
5,747,206
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top