Validation in UserForm

[kimberly090]

Hi,

I have 2 sheet in my workbook.

The first worksheet "Inventory" contain the incoming stock.

The second worksheet "Track Out" will contain the outgoing stock.

I have create a userform in my Track Out sheet like this:

and my Track Out list will look like this:

Here will be my inventory list:

I would like to find out how can I validate the data in Track Out page. For example when I type in the PT# in my userform, if the PT# is not exist inside Inventory list, it will not allow user to click on the Track Out button in the userform.

Here will be the code for my userform:

Private Sub TrackOut_Click()Dim cDelete As VbMsgBoxResult
                   With Me
                            If Len(.TextBox1.Value) * Len(.PTD.Value) * Len(.RackD.Value) * _
                Len(.OperatorD.Value) = 0 Then
            MsgBox "Please Complete All Fields Before Submit"
        Else
        
        cDelete = MsgBox("Are you sure that you want to delete this record", vbYesNo + vbDefaultButton2, "Track Out")
        If cDelete = vbYes Then
           eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 1).Value = TextBox1.Text
            Cells(eRow, 2).Value = PTD.Text
            Cells(eRow, 3).Value = RackD.Text
            Cells(eRow, 4).Value = OperatorD.Text
            
            Else
            
            If cDelete = vbNo Then
            Unload Me
            End If
            End If
        End If
    End With
   
End Sub

Thank you.

 

[dya]

After your If cDelete = vbYes Then line, I'd add another IF statement nested within that one. Try:

If Sheets("Inventory").Columns(2).Find(What:=PTD.Text) Is Nothing Then
    MsgBox "No stock for this PT#"
    exit sub
End If

All together now:

Private Sub TrackOut_Click()Dim cDelete As VbMsgBoxResult
                   With Me
                            If Len(.TextBox1.Value) * Len(.PTD.Value) * Len(.RackD.Value) * _
                Len(.OperatorD.Value) = 0 Then
            MsgBox "Please Complete All Fields Before Submit"
        Else
        
        cDelete = MsgBox("Are you sure that you want to delete this record", vbYesNo + vbDefaultButton2, "Track Out")
        If cDelete = vbYes Then
           If Sheets("Inventory").Columns(2).Find(What:=PTD.Text) Is Nothing Then
              MsgBox "No stock for this PT#"
              Exit Sub
           End If
           eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 1).Value = TextBox1.Text
            Cells(eRow, 2).Value = PTD.Text
            Cells(eRow, 3).Value = RackD.Text
            Cells(eRow, 4).Value = OperatorD.Text
            
            Else
            
            If cDelete = vbNo Then
            Unload Me
            End If
            End If
        End If
    End With
   
End Sub

 

[kimberly090]

After your If cDelete = vbYes Then line, I'd add another IF statement nested within that one. Try:

If Sheets("Inventory").Columns(2).Find(What:=PTD.Text) Is Nothing Then
    MsgBox "No stock for this PT#"
    exit sub
End If

All together now:

Private Sub TrackOut_Click()Dim cDelete As VbMsgBoxResult
                   With Me
                            If Len(.TextBox1.Value) * Len(.PTD.Value) * Len(.RackD.Value) * _
                Len(.OperatorD.Value) = 0 Then
            MsgBox "Please Complete All Fields Before Submit"
        Else
        
        cDelete = MsgBox("Are you sure that you want to delete this record", vbYesNo + vbDefaultButton2, "Track Out")
        If cDelete = vbYes Then
           If Sheets("Inventory").Columns(2).Find(What:=PTD.Text) Is Nothing Then
              MsgBox "No stock for this PT#"
              Exit Sub
           End If
           eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 1).Value = TextBox1.Text
            Cells(eRow, 2).Value = PTD.Text
            Cells(eRow, 3).Value = RackD.Text
            Cells(eRow, 4).Value = OperatorD.Text
            
            Else
            
            If cDelete = vbNo Then
            Unload Me
            End If
            End If
        End If
    End With
   
End Sub

yes! is working. Thank you so much.

 

[Andrew Poulsom]

Why don't you use a ComboBox filled with the PT#s from the Stock In worksheet? Then you won't have to validate the user's selection.