Snasui Excel Forum

Code: Select all

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"))
     If MsgBox("Do you want to delete the data? ", vbYesNo + vbQuestion + vbDefaultButton2, " Close and Save ") = 6 Then
     '....
     End If
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(CDbl(Me.TextBox5.Text), rngVlp, 2, 0)
    Me.TextBox7.Text = Application.VLookup(CDbl(Me.TextBox5.Text), rngVlp, 3, 0)
    Me.TextBox8.Text = Application.VLookup(CDbl(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

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

Code: Select all

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

Code: Select all

Private Sub TextBox5_AfterUpdate()
    Dim rngCmpr As Range
    Dim rngBrcd As Range
    With Workbooks("DataX.xlsx").Worksheets("Sheet1")
        Set rngvlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
        Set rngBrcd = rngvlp.Resize(, 1)
        Set rngCmpr = .Range("g2", .Range("g" & .Rows.Count).End(xlUp))
    End With
    With Application
        If .CountIf(rngCmpr, Me.TextBox5.Text) > 0 And _
            .CountIf(rngBrcd, Me.TextBox5.Text) > 2 Then
            MsgBox "<Your message>", vbInformation
            Exit Sub
        End If
    End With
'Other code

Code: Select all

 If .CountIf(rngCmpr, Me.TextBox5.Text) > 0 And _
.CountIf(rngBrcd, Me.TextBox5.Text) > 2 Then

Code: Select all

    Dim rngCmpr As Range
    Dim rngBrcd As Range
    With Workbooks("DataX.xlsx").Worksheets("Sheet1")
        Set rngvlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
        Set rngBrcd = rngvlp.Resize(, 1)
        Set rngCmpr = .Range("f2", .Range("f" & .Rows.Count).End(xlUp))
    End With
    With Application
        If .CountIf(rngCmpr, Me.TextBox5.Text) < 0 And _
            .CountIf(rngBrcd, Me.TextBox5.Text) < 2 Then
            MsgBox "<Your message>", vbInformation
            Exit Sub
        End If
    End With
'Other code