Snasui Excel Forum

Code: Select all

Private Sub Cmdpre_Click()
'nextRow = Worksheets("DataInput").Columns(3).Find(ComboBox2.Text).Row
With Worksheets("DataInput")
    nextrow = Application.Match(ComboBox2.Text, .Range("c:c"), 0) - 1
End With
If Cells(nextrow, 2) = "ลำดับ" Then
MsgBox "Can't previous because last one"
GoTo NextStep

End If
'nextRow = ActiveCell.Row - 1
'Cells(nextRow, 1).Activate
    ComboBox2.Text = Sheets("DataInput").Cells(nextrow, 3)
'Other code

Code: Select all

Private Sub Cmdpre_Click()
nextRow = Worksheets("DataInput").Columns(3).Find(ComboBox2.Text).Row
If Cells(nextRow - 1, 2) = "ลำดับ" Then
MsgBox "Can't previous because last one"
GoTo NextStep

End If
nextRow = ActiveCell.Row - 1
Cells(nextRow, 1).Activate
    Textpo.Value = Sheets("DataInput").Cells(nextRow, 3)
    Textpr.Value = Sheets("DataInput").Cells(nextRow, 4)
    Textdate.Value = Sheets("DataInput").Cells(nextRow, 5)
    ComboBox1.Value = Sheets("DataInput").Cells(nextRow, 6)
    TextSup.Value = Sheets("DataInput").Cells(nextRow, 7)
    TextPrice.Value = Sheets("DataInput").Cells(nextRow, 8)
    Textqty.Value = Sheets("DataInput").Cells(nextRow, 9)
    Textqtyrec.Value = Sheets("DataInput").Cells(nextRow, 10)
    TextDue.Value = Sheets("DataInput").Cells(nextRow, 11)
    Textdaterec.Value = Sheets("DataInput").Cells(nextRow, 12)

NextStep:

End Sub

Code: Select all

'Other code
If Application.CountIf(Worksheets("DataInput").Range("c:c"), Textpo.Text) > 0 Then
    irow = Application.Match((Textpo.Text), Worksheets("DataInput").Range("c:c"), 0)
    msgRepns = MsgBox("เลขที่ PO นี้มีอยู่แล้วถ้าต้องการแก้ไขข้อมูลคลิก Yes หากต้องการยกเลิก? NO", vbYesNo)
Else
    irow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
End If
'Other code

Code: Select all

Private Sub CommandButton2_Click()
    Dim irow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("DataInput")
    
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
        
    'Check for Id
    If (Me.Textpo.Text) = "" Then
        Me.Textpo.SetFocus
    Exit Sub
   End If
    If Application.CountIf(Range("c:c"), Textpo.Text) > 0 Then
        irow = Application.Match((Textpo.Text), Range("c:c"), 0)
        msgRepns = MsgBox("เลขที่ PO นี้มีอยู่แล้วถ้าต้องการแก้ไขข้อมูลคลิก Yes หากต้องการยกเลิกให้คลิก NO", vbYesNo)
    Else
        irow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    End If
    If msgRepns = vbNo Then
        Exit Sub
    End If
    
    'copy the data to the database
    ws.Cells(irow, 3).Value = Me.Textpo.Value
    ws.Cells(irow, 4).Value = Me.Textpr.Value
    ws.Cells(irow, 5).Value = Format(Me.Textdate.Value, "dd-mmm-yyyy")
    ws.Cells(irow, 6).Value = Me.ComboBox1.Value
    ws.Cells(irow, 7).Value = Me.TextSup.Value
    ws.Cells(irow, 8).Value = Me.TextPrice.Value
    ws.Cells(irow, 9).Value = Me.Textqty.Value
    ws.Cells(irow, 10).Value = Me.Textqtyrec.Value
    ws.Cells(irow, 11).Value = Format(Me.TextDue.Value, "dd-mmm-yyyy")
    ws.Cells(irow, 12).Value = Format(Me.Textdaterec.Value, "dd-mmm-yyyy")
    Unload Me

    'Clear the data
    Me.Textpo.Value = ""
    Me.Textpr.Value = ""
    Me.Textdate.Value = ""
    Me.ComboBox1.Value = ""
    Me.TextSup.Value = ""
    Me.TextPrice.Value = ""
    Me.Textqty.Value = ""
    Me.Textqtyrec.Value = ""
    Me.TextDue.Value = ""
    Me.Textdaterec.Value = ""
    MsgBox "บันทึกข้อมูลสำเร็จ"


End Sub