Snasui Excel Forum

Code: Select all

Private Sub TextBox5_AfterUpdate()
    If Me.TextBox5.Text = "" Then Exit Sub
    If WorksheetFunction.CountIf(Sheets("Data").Range("A:A"), Me.TextBox5.Value) = 0 Then
        'Me.TextBox5.Value = ""
        MsgBox "Not found."
        Exit Sub
    End If
    emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
    
    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
    With Worksheets("IN")
        .Cells(emptyrow, 1).Value = TextBox1.Value
        .Cells(emptyrow, 2).Value = TextBox2.Value
        .Cells(emptyrow, 3).Value = TextBox4.Value
        .Cells(emptyrow, 4).Value = ComboBox1.Value
        .Cells(emptyrow, 5).Value = TextBox5.Value
        .Cells(emptyrow, 6).Value = TextBox6.Value
        .Cells(emptyrow, 7).Value = TextBox7.Value
        .Cells(emptyrow, 8).Value = TextBox8.Value
        .Cells(emptyrow, 9).Value = TextBox9.Value
    End With
End Sub

Code: Select all

dim rngVlp as range
with workbooks("DataX.xlsx").worksheets(1)
   set rngVlp = .range("a2",.range("z" & .rows.count).end(xlup))
end with
me.textbox1.text = application.vlookup(clng(me.textbox5.text),rngVlp,2,0)

Code: Select all

Private Sub TextBox5_AfterUpdate()
Dim rngVlp As Range
With Workbooks("C:\Users\Administrator\Desktop\Test\DataX.xlsx").worksheets(sheet1)
Set rngVlp = .Range("A", .Range("D" & .Rows.Count).End(xlUp))
          'Me.TextBox5.Value = ""
MsgBox "Not found."
        Exit Sub

emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
End With
Me.TextBox6.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 2, 0)
Me.TextBox7.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 3, 0)
Me.TextBox8.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 4, 0)

    With Worksheets("IN")
        .Cells(emptyrow, 1).Value = TextBox1.Value
        .Cells(emptyrow, 2).Value = TextBox2.Value
        .Cells(emptyrow, 3).Value = TextBox4.Value
        .Cells(emptyrow, 4).Value = ComboBox1.Value
        .Cells(emptyrow, 5).Value = TextBox5.Value
        .Cells(emptyrow, 6).Value = TextBox6.Value
        .Cells(emptyrow, 7).Value = TextBox7.Value
        .Cells(emptyrow, 8).Value = TextBox8.Value
        .Cells(emptyrow, 9).Value = TextBox9.Value
    End With
End Sub

Code: Select all

Private Sub TextBox5_AfterUpdate()
Dim rngVlp As Range
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
Set rngVlp = .Range("A1", .Range("D1048576" & .Rows.Count).End(xlUp))
End With
Me.TextBox6.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 2, 0)
Me.TextBox7.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 3, 0)
Me.TextBox8.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 4, 0)

    With Worksheets("IN")
        .Cells(emptyrow, 1).Value = TextBox1.Value
        .Cells(emptyrow, 2).Value = TextBox2.Value
        .Cells(emptyrow, 3).Value = TextBox4.Value
        .Cells(emptyrow, 4).Value = ComboBox1.Value
        .Cells(emptyrow, 5).Value = TextBox5.Value
        .Cells(emptyrow, 6).Value = TextBox6.Value
        .Cells(emptyrow, 7).Value = TextBox7.Value
        .Cells(emptyrow, 8).Value = TextBox8.Value
        .Cells(emptyrow, 9).Value = TextBox9.Value
    End With
End Sub

Code: Select all

Private Sub TextBox5_AfterUpdate()
Dim rngVlp As Range
With Workbooks("C:\Users\Administrator\Desktop\Test\DataX.xlsx").Worksheets("Sheet1")
Set rngVlp = .Range("A2", .Range("D1048576" & .Rows.Count).End(xlUp))
End With
Me.TextBox6.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 2, 0)
Me.TextBox7.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 3, 0)
Me.TextBox8.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 4, 0)

    With Worksheets("IN")
        .Cells(emptyrow, 1).Value = TextBox1.Value
        .Cells(emptyrow, 2).Value = TextBox2.Value
        .Cells(emptyrow, 3).Value = TextBox4.Value
        .Cells(emptyrow, 4).Value = ComboBox1.Value
        .Cells(emptyrow, 5).Value = TextBox5.Value
        .Cells(emptyrow, 6).Value = TextBox6.Value
        .Cells(emptyrow, 7).Value = TextBox7.Value
        .Cells(emptyrow, 8).Value = TextBox8.Value
        .Cells(emptyrow, 9).Value = TextBox9.Value
    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

Code: Select all

With Workbooks("DataX.xlsx").Worksheets("Sheet1")

Code: Select all

Private Sub TextBox5_AfterUpdate()
Dim rngVlp As Range
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
Set rngVlp = .Range("a2", .Range("z" & .Rows.Count).End(xlUp))
End With
Me.TextBox6.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 2, 0)
Me.TextBox7.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 3, 0)
Me.TextBox8.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 4, 0)

    With Worksheets("IN")
        .Cells(emptyrow, 1).Value = TextBox1.Value
        .Cells(emptyrow, 2).Value = TextBox2.Value
        .Cells(emptyrow, 3).Value = TextBox4.Value
        .Cells(emptyrow, 4).Value = ComboBox1.Value
        .Cells(emptyrow, 5).Value = TextBox5.Value
        .Cells(emptyrow, 6).Value = TextBox6.Value
        .Cells(emptyrow, 7).Value = TextBox7.Value
        .Cells(emptyrow, 8).Value = TextBox8.Value
        .Cells(emptyrow, 9).Value = TextBox9.Value
    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

Code: Select all

'Other code...
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
    Set rngVlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
    Me.TextBox6.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 2, 0)
    Me.TextBox7.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 3, 0)
    Me.TextBox8.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 4, 0)
End With
With ThisWorkbook.Worksheets("IN")
    emptyrow = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).Row
'Other code...

Code: Select all

Private Sub TextBox5_AfterUpdate()
Dim rngVlp As Range
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
Set rngVlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
If Me.TextBox5.Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Workbooks("DataX.xlsx").Worksheets("Sheet1").Range("A:D"), Me.TextBox5.Value) = 0 Then
'Me.TextBox5.Value = ""
MsgBox "Not found."
Exit Sub
End If
    Me.TextBox6.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 2, 0)
    Me.TextBox7.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 3, 0)
    Me.TextBox8.Text = Application.VLookup(CLng(Me.TextBox5.Text), rngVlp, 4, 0)
End With
With ThisWorkbook.Worksheets("IN")
    emptyrow = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).Row
   End With
    With Worksheets("IN")
        .Cells(emptyrow, 1).Value = TextBox1.Value
        .Cells(emptyrow, 2).Value = TextBox2.Value
        .Cells(emptyrow, 3).Value = TextBox4.Value
        .Cells(emptyrow, 4).Value = ComboBox1.Value
        .Cells(emptyrow, 5).Value = TextBox5.Value
        .Cells(emptyrow, 6).Value = TextBox6.Value
        .Cells(emptyrow, 7).Value = TextBox7.Value
        .Cells(emptyrow, 8).Value = TextBox8.Value
        .Cells(emptyrow, 9).Value = TextBox9.Value
    End With
End Sub

Code: Select all

Private Sub Workbook_Open()

Worksheets("IN").Select
Workbooks.Open Filename:="C:\Users\Administrator\Desktop\Test\DataX.xlsx"
ADD.Show
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Visible = False

End Sub