Snasui Excel Forum

Code: Select all

Sub SearchData_recives()

    Application.ScreenUpdating = False
    
    Dim shDatabase As Worksheet ' Database sheet
    Dim shSearchData As Worksheet 'SearchData sheet
    
    Dim iColumn As Integer 'To hold the selected column number in Database sheet
    Dim iDatabaseRow As Long 'To store the last non-blank row number available in Database sheet
    Dim iSearchRow As Long 'To hold the last non-blank row number available in SearachData sheet
    
    Dim sColumn As String 'To store the column selection
    Dim sValue As String 'To hold the search text value
    
    Set shDatabase = ThisWorkbook.Sheets("DatabaseOUT")
    Set shSearchData = ThisWorkbook.Sheets("SearchDataOUT")
    
    iDatabaseRow = ThisWorkbook.Sheets("DatabaseOUT").Range("A" & Application.Rows.Count).End(xlUp).Row
    
    sColumn = frmRecives.cmbrecivesSearchColumn.value
    
    sValue = frmRecives.recivestxtSearch.value
    
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:L1"), 0)
    
    'Remove filter from Database worksheet
    
    If shDatabase.FilterMode Then
    shDatabase.AutoFilterMode = False
    'shDatabase.ShowAllData
    shDatabase.ListObjects("Table3").AutoFilter.ShowAllData
End If

    'Apply filter on Database worksheet
    
    If frmRecives.cmbrecivesSearchColumn.value = "เลขทะเบียนส่ง" Then

        shDatabase.Range("A1:L" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
    
    Else
    
        shDatabase.Range("A1:L" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    
    End If
    
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Columns(iColumn)) >= 2 Then
    
        'Code to remove the previous data from SearchData worksheet
        
        shSearchData.Cells.Clear
        
        shDatabase.UsedRange.Copy shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        
        frmRecives.recDatabase.ColumnCount = 12
        
        frmRecives.recDatabase.ColumnWidths = "40,70,75,75,75,80,70,70,70,350,70,70"
        
        If iSearchRow > 1 Then
        
            frmRecives.recDatabase.RowSource = "SearchDataOUT!A2:L" & iSearchRow
            
            MsgBox "พบข้อมูลที่ค้นหา!!!"
        
        End If
        
    Else
    
       MsgBox "ไม่พบข้อมูลที่ค้นหา"
    
    End If

    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub

Code: Select all

Private Sub cmdEditrecives_Click()
    
    If Selected_List = 0 Then
    
        MsgBox "ไม่ได้เลือกข้อมูล", vbOKOnly + vbInformation, "แก้ไข"
        
        Exit Sub
    
    End If
    
    'Code to update the value to respective controls
    
    Me.recivestxtRowNumber.value = Selected_List + 1
    
    Me.recivestxtID.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 1)
    
    Me.recivestxtName.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 2)
    
    Me.recivestxtDate.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 3)
    
    Me.cmbrecivesfrom.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 4)
    
    Me.cmbrecivesto.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 5)
    
    Me.cmbrecivesclass.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 6)
    
    Me.cmbrecivesspeed.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 7)
    
    Me.cmbrecivessecurity.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 8)
    
    Me.recivestxtSubj.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 9)
    
    Me.recivestxtJobn.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 10)
    
    Me.recivestxtN.value = Me.recDatabase.List(Me.recDatabase.ListIndex, 11)
    
    MsgBox "หลังจากแก้ไขข้อมูลเสร็จสิ้น ให้กดปุ่มบันทึกข้อมูล!!!", vbOKOnly + vbInformation, "แก้ไข"
    
End Sub

Code: Select all

Private Sub cmdDeleteRecives_Click()
    
    If Selected_List = 0 Then
    
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "ลบ"
        Exit Sub
    End If
    
    Dim i As VbMsgBoxResult
    
    i = MsgBox("คุณมั่นใจที่จะลบหรือไม่?", vbYesNo + vbQuestion, "Confirmation")
    
    If i = vbNo Then Exit Sub
    
    ThisWorkbook.Sheets("DatabaseOUT").Rows(Selected_List + 1).Delete
    
    Call Resetrecives
    
    MsgBox "เลือกข้อมูลที่ต้องการลบ", vbOKOnly + vbInformation, "ลบแล้ว!!!"
    
End Sub

Code: Select all

Private Sub cmdSearchrecives_Click()

    If Me.recivestxtSearch.value = "" Then
    
        MsgBox "ãÊè¢éͤÇÒÁ·Õèµéͧ¡Òäé¹ËÒ", vbOKOnly + vbInformation, "¤é¹ËÒ"
        Exit Sub
    
    End If
    
    Call SearchData_recives
     
End Sub

Code: Select all

Private Sub cmdSearchrecives_Click()

    If Me.recivestxtSearch.value = "" Then
    
        MsgBox "ใส่ข้อความที่ต้องการค้นหา", vbOKOnly + vbInformation, "ตกลง"
        Exit Sub
    
    End If
    
    Call SearchData_recives
     
End Sub