Excel Forum

Code: Select all

Private Sub CmdSave_Click()
Dim irow As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row
        
    'Check for Id
    If (Me.TxtID.Value) = "" Then
        Me.TxtID.SetFocus
    Exit Sub
    End If
If Application.CountIf(Range("b:b"), TxtID.Text) > 0 Then
    irow = Application.Match(TxtID.Text, Range("b:b"), 0)
Else
    irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If
   MsgBox "ÁÕ¢éÍÁÙżÙé»èÇÂáÅéÇ"
   Exit Sub

    'copy the data to the database
    ws.Cells(irow, 2).Value = Me.TxtID.Value
    ws.Cells(irow, 3).Value = Me.listTitle.Value
    ws.Cells(irow, 4).Value = Me.txtName.Value
    ws.Cells(irow, 5).Value = Me.txtSurename.Value
    ws.Cells(irow, 6).Value = Me.liststatus.Value
    ws.Cells(irow, 7).Value = Me.txtMobile.Value
    Unload Me

    'Clear the data
    Me.TxtID.Value = ""
    Me.listTitle.Value = ""
    Me.txtName.Value = ""
    Me.txtSurename.Value = ""
    Me.liststatus.Value = ""
    Me.txtMobile.Value = ""
    Me.TextBox1.SetFocus
    
 
End Sub

Code: Select all

Private Sub CmdSave_Click()
    Dim irow As Integer
    Dim msgRepns As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row
        
    'Check for Id
    If (Me.TxtID.Value) = "" Then
        Me.TxtID.SetFocus
    Exit Sub
    End If
    If Application.CountIf(Range("b:b"), TxtID.Text) > 0 Then
        irow = Application.Match(CDbl(TxtID.Text), Range("b:b"), 0)
        msgRepns = MsgBox("มีข้อมูลผู้ป่วยแล้ว หากต้องการแก้ไขข้อมูลเดิมคลิก Yes หากต้องการยกเลิกคลิก NO", vbYesNo)
    Else
        irow = ws.Cells(Rows.Count, 1).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, 2).Value = Me.TxtID.Value
    ws.Cells(irow, 3).Value = Me.listTitle.Value
    ws.Cells(irow, 4).Value = Me.txtName.Value
    ws.Cells(irow, 5).Value = Me.txtSurename.Value
    ws.Cells(irow, 6).Value = Me.liststatus.Value
    ws.Cells(irow, 7).Value = Me.txtMobile.Value
    Unload Me

    'Clear the data
    Me.TxtID.Value = ""
    Me.listTitle.Value = ""
    Me.txtName.Value = ""
    Me.txtSurename.Value = ""
    Me.liststatus.Value = ""
    Me.txtMobile.Value = ""
    MsgBox "บันทึกสำเร็จ"
End Sub

Code: Select all

Private Sub CmdFind_Click()

On Error Resume Next
'Err.Clear
nRow = Workbooks("Employee Data.xlsx").Worksheets("Data").Columns(4).Find(Txtfind.Text).Row

If Err.Number = 91 Then

    TxtID.Value = "Not Found"
    listTitle.Value = "Not Found"
    txtName.Value = "Not Found"
    txtSurename.Value = "Not Found"
    liststatus.Value = "Not Found"
    txtMobile.Value = "Not Found"
       MsgBox "Don't have this Name"
    GoTo nNextี
End If

    TxtID.Value = Cells(nRow, 2)
    listTitle.Value = Cells(nRow, 3)
    txtName.Value = Cells(nRow, 4)
    txtSurename.Value = Cells(nRow, 5)
    liststatus.Value = Cells(nRow, 6)
    txtMobile.Value = Format(Cells(nRow, 7), "000-0000000")
    'current address
    Txtno1.Value = Cells(nRow, 9)
    TxtMoo1.Value = Cells(nRow, 10)
    Txtban1.Value = Cells(nRow, 11)
    Txtsoi1.Value = Cells(nRow, 12)
    Txtroad1.Value = Cells(nRow, 13)
    listtambon1.Value = Cells(nRow, 14)
    listampor1.Value = Cells(nRow, 15)
    listprovince1.Value = Cells(nRow, 16)
    Txtcode1.Value = Cells(nRow, 17)
    'home address
    Txtno2.Value = Cells(nRow, 18)
    TxtMoo2.Value = Cells(nRow, 19)
    Txtban2.Value = Cells(nRow, 20)
    Txtsoi2.Value = Cells(nRow, 21)
    Txtroad2.Value = Cells(nRow, 22)
    listtambon2.Value = Cells(nRow, 23)
    listampor2.Value = Cells(nRow, 24)
    listprovince2.Value = Cells(nRow, 25)
    Txtcode2.Value = Cells(nRow, 26)
    Cells(nRow, 2).Activate
    
nNext:
End Sub

Private Sub CmdSave_Click()
    Dim irow As Integer
    Dim msgRepns As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row
        
    'Check for Id
    If (Me.TxtID.Value) = "" Then
        Me.Txtfind.SetFocus
       
    Exit Sub
    End If
    If Application.CountIf(Range("b:b"), TxtID.Text) > 0 Then
        irow = Application.Match(CDbl(TxtID.Text), Range("b:b"), 0)
        msgRepns = MsgBox("ต้องการแก้ไขข้อมูล ?", vbYesNo)
    Else
        irow = ws.Cells(Rows.Count, 1).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, 2).Value = Me.TxtID.Value
    ws.Cells(irow, 3).Value = Me.listTitle.Value
    ws.Cells(irow, 4).Value = Me.txtName.Value
    ws.Cells(irow, 5).Value = Me.txtSurename.Value
    ws.Cells(irow, 6).Value = Me.liststatus.Value
    ws.Cells(irow, 7).Value = Format(Me.txtMobile.Value, "000-0000000")
    'current address
    ws.Cells(irow, 9).Value = Me.Txtno1.Value
    ws.Cells(irow, 10).Value = Me.TxtMoo1.Value
    ws.Cells(irow, 11).Value = Me.Txtban1.Value
    ws.Cells(irow, 12).Value = Me.Txtsoi1.Value
    ws.Cells(irow, 13).Value = Me.Txtroad1.Value
    ws.Cells(irow, 14).Value = Me.listtambon1.Value
    ws.Cells(irow, 15).Value = Me.listampor1.Value
    ws.Cells(irow, 16).Value = Me.listprovince1.Value
    ws.Cells(irow, 17).Value = Me.Txtcode1.Value
    'home address
    ws.Cells(irow, 18).Value = Me.Txtno2.Value
    ws.Cells(irow, 19).Value = Me.TxtMoo2.Value
    ws.Cells(irow, 20).Value = Me.Txtban2.Value
    ws.Cells(irow, 21).Value = Me.Txtsoi2.Value
    ws.Cells(irow, 22).Value = Me.Txtroad2.Value
    ws.Cells(irow, 23).Value = Me.listtambon2.Value
    ws.Cells(irow, 24).Value = Me.listampor2.Value
    ws.Cells(irow, 25).Value = Me.listprovince2.Value
    ws.Cells(irow, 26).Value = Me.Txtcode2.Value
    'Unload Me
     ws.Cells(irow, 27).Value = Format(Me.TxtDate.Value, "dd/mm/yyyy")
    'Clear the data
    Me.TxtID.Value = ""
    Me.listTitle.Value = ""
    Me.txtName.Value = ""
    Me.txtSurename.Value = ""
    Me.liststatus.Value = ""
    Me.txtMobile.Value = ""
    Me.Txtfind.Value = ""
    Me.Txtno1.Value = ""
    Me.TxtMoo1.Value = ""
    Me.Txtban1.Value = ""
    Me.Txtsoi1.Value = ""
    Me.Txtroad1.Value = ""
    Me.listtambon1.Value = ""
    Me.listampor1.Value = ""
    Me.listprovince1.Value = ""
    Me.Txtcode1.Value = ""
    Me.Txtno2.Value = ""
    Me.TxtMoo2Value = ""
    Me.Txtban2.Value = ""
    Me.Txtsoi2.Value = ""
    Me.Txtroad2.Value = ""
    Me.listtambon2.Value = ""
    Me.listampor2.Value = ""
    Me.listprovince2.Value = ""
    Me.Txtcode2.Value = ""
    
    MsgBox "บันทึกสำเร็จ"
End Sub

Code: Select all

Private Sub CmdFind_Click()
Dim wb As Workbook
On Error Resume Next
'Err.Clear
Set wb = Workbooks.Open("C:\Users\THA0753H\Desktop\Employee Data.xlsx")
nRow = wb.Worksheets("Data").Columns(4).Find(Txtfind.Text).Row
'Other code
wb.Close false
End sub