Snasui Excel Forum

Code: Select all

If MsgBox(" Finish this carton ?", vbYesNo + vbQuestion + vbDefaultButton2, " Close and Save ") = 6 Then
Sheets("in").Select
Range("A3:D1048576").Select
Do While ActiveCell <> ""
ActiveCell.Offset(0, 0).EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Loop
End If

Code: Select all

'Other code...
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
    Set rngVlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
End With
With ThisWorkbook.Worksheets("IN")
    emptyrow = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).Row
End With
If Me.TextBox5.Text = "" Then Exit Sub
If Me.TextBox5.Text = "5555" Then
    Worksheets("IN").Cells(emptyrow - 1, 1).EntireRow.Delete
End If
'Other code...

Code: Select all

Private Sub TextBox5_AfterUpdate()
If MsgBox(" Finish this carton ?", vbYesNo + vbQuestion + vbDefaultButton2, " Close and Save ") = 6 Then
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
    Set rngVlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
End With
With ThisWorkbook.Worksheets("IN")
    emptyrow = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).Row
End With
If Me.TextBox5.Text = "" Then Exit Sub
If Me.TextBox5.Text = "10020" Then
    Worksheets("IN").Cells(emptyrow - 1, 1).EntireRow.Delete
    End If
   End If
Dim SoundFile As String, rc As Long
SoundFile = "C:\Users\Administrator\Downloads\A_Drum.wav"
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

If Me.TextBox5.Text = "10020" Then
    Worksheets("IN").Cells(emptyrow - 1, 1).EntireRow.Delete
     If MsgBox(" Finish this carton ?", vbYesNo + vbQuestion + vbDefaultButton2, " Close and Save ") = 6 Then
     '....
     End If    
End If

Code: Select all

'other
If Me.TextBox5.Text = "10020" Then
    Worksheets("IN").Cells(emptyrow - 1, 1).EntireRow.Delete
     If MsgBox(" Finish this carton ?", vbYesNo + vbQuestion + vbDefaultButton2, " Close and Save ") = 6 Then
     '....
     End If
End If
'other

Code: Select all

Worksheets("IN").Cells(emptyrow - 1, 1).EntireRow.Delete

Code: Select all

Private Sub TextBox2_AfterUpdate()
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
    Set rngVlp = .Range("E2", .Range("F" & .Rows.Count))
If Me.TextBox2.Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Workbooks("DataX.xlsx").Worksheets("Sheet1").Range("E:F"), Me.TextBox2.Value) = 0 Then
Call Sample2
'Me.TextBox2.Value = ""
MsgBox "Not found."
Exit Sub
End If
    Me.TextBox10 = Application.VLookup(CLng(Me.TextBox2.Text), rngVlp, 2, 0)
End With
End Sub

Private Sub TextBox5_AfterUpdate()
With Workbooks("DataX.xlsx").Worksheets("Sheet1")
Set rngVlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
End With

With ThisWorkbook.Worksheets("IN")
emptyrow = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).Row
End With

If Me.TextBox5.Text = "10021" Then
Worksheets("IN").Cells(emptyrow - 1, 1).EntireRow.Delete
  Me.TextBox11.Text = Application.WorksheetFunction.Sum(Range("H3:H1048576"))
End If

If Me.TextBox5.Text = "" Then Exit Sub
If WorksheetFunction.CountIfs(Workbooks("DataX.xlsx").Worksheets("Sheet1").Range("A:A"), Me.TextBox5.Value, _
Workbooks("DataX.xlsx").Worksheets("Sheet1").Range("E:E"), Me.TextBox2.Value) = 0 Then
Call Sample2
'Me.TextBox5.Value = ""
MsgBox "Please Check Information"
    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)
    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 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 = ComboBox1.Value
        .Cells(emptyrow, 4).Value = TextBox5.Value
        .Cells(emptyrow, 5).Value = TextBox6.Value
        .Cells(emptyrow, 6).Value = TextBox7.Value
        .Cells(emptyrow, 7).Value = TextBox8.Value
        .Cells(emptyrow, 8).Value = TextBox9.Value
    End With
    
    Me.TextBox11.Text = Application.WorksheetFunction.Sum(Range("H3:H1048576"))
Dim total As Long
'Me.TextBox5.Value = ""
total = WorksheetFunction.Sum(Range("H3:H1048576"))
Range("A1").Value = total
Range("C1") = TextBox10.Value
If Range("A1").Value > Range("C1") Then
Call Sample2
MsgBox "TOTAL NUMBER ERROR"
End If
        Dim lsRow As Long
    With Sheets("IN")
        lsRow = .Range("a" & .Rows.Count).End(xlUp).Row
    End With
     ListBox1.RowSource = Sheets("IN").Range("A3:H" & lsRow).Address(external:=True)
    With ListBox1
        .ListIndex = .ListCount - 1
        .Selected(.ListCount - 1) = True
    End With
End Sub

Code: Select all

Dim i&
With ActiveSheet
If Me.TextBox5.Text = "10021" Then
 i = Worksheets("IN").Cells(emptyrow - 1, 1).EntireRow.Delete
End If
Me.TextBox11.Text = Application.WorksheetFunction.Sum(Range("H3:H1048576"))