Snasui Excel Forum

Code: Select all

Private Sub TextBox5_AfterUpdate()

If WorksheetFunction.CountIf(Sheets("Data").Range("A:D"), Me.TextBox5.Value) = 0 Then
Me.TextBox5.Value = ""
End If
With Me
.TextBox6 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 2, 0)
.TextBox7 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 3, 0)
.TextBox8 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 4, 0)
End With

If Me.TextBox5.Text = "" Then Exit Sub
    emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
    Worksheets("IN").Cells(emptyrow, 1).Value = TextBox1.Value
    Worksheets("IN").Cells(emptyrow, 2).Value = TextBox2.Value
    Worksheets("IN").Cells(emptyrow, 3).Value = TextBox4.Value
    Worksheets("IN").Cells(emptyrow, 4).Value = ComboBox1.Value
    Worksheets("IN").Cells(emptyrow, 5).Value = TextBox5.Value
    Worksheets("IN").Cells(emptyrow, 6).Value = TextBox6.Value
    Worksheets("IN").Cells(emptyrow, 7).Value = TextBox7.Value
    Worksheets("IN").Cells(emptyrow, 8).Value = TextBox8.Value
    Worksheets("IN").Cells(emptyrow, 9).Value = TextBox9.Value
    End Sub

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox5.Value <> "" Then
        Cancel = True
        TextBox5.Text = ""
    End If
End Sub

Code: Select all

Private Sub TextBox5_AfterUpdate()

If Me.TextBox5.Text = "" Then Exit Sub
    emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
    Worksheets("IN").Cells(emptyrow, 1).Value = TextBox1.Value
    Worksheets("IN").Cells(emptyrow, 2).Value = TextBox2.Value
    Worksheets("IN").Cells(emptyrow, 3).Value = TextBox4.Value
    Worksheets("IN").Cells(emptyrow, 4).Value = ComboBox1.Value
    Worksheets("IN").Cells(emptyrow, 5).Value = TextBox5.Value
    Worksheets("IN").Cells(emptyrow, 6).Value = TextBox6.Value
    Worksheets("IN").Cells(emptyrow, 7).Value = TextBox7.Value
    Worksheets("IN").Cells(emptyrow, 8).Value = TextBox8.Value
    Worksheets("IN").Cells(emptyrow, 9).Value = TextBox9.Value

If WorksheetFunction.CountIf(Sheets("Data").Range("A:D"), Me.TextBox5.Value) = 0 Then
Me.TextBox5.Value = ""
End If
With Me
.TextBox6 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 2, 0)
.TextBox7 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 3, 0)
.TextBox8 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 4, 0)
End With

    End Sub


Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox5.Value <> "" Then
        Cancel = True
        TextBox5.Text = ""
    End If
End Sub