Snasui Excel Forum

Code: Select all

https://snasui.com/viewtopic.php?f=3&t=18301

Code: Select all

Option Explicit
Dim FormEvents As Boolean
Private Sub ClearForm(Except As String)
    Select Case Except
    
        Case "ReceiveNO"
            FormEvents = False
            BookNO.value = ""
            DateIN.value = ""
            From.value = ""
            Too.value = ""
            Detail.value = ""
            Results.Clear
            FormEvents = True

        Case "BookNO"
            FormEvents = False
            ReceiveNO.value = ""
            DateIN.value = ""
            From.value = ""
            Too.value = ""
            Detail.value = ""
            Results.Clear
            FormEvents = True

        Case "DateIN"
            FormEvents = False
            ReceiveNO.value = ""
            BookNO.value = ""
            From.value = ""
            Too.value = ""
            Detail.value = ""
            Results.Clear
            FormEvents = True

        Case "From"
            FormEvents = False
            ReceiveNO.value = ""
            BookNO.value = ""
            DateIN.value = ""
            Too.value = ""
            Detail.value = ""
            Results.Clear
            FormEvents = True
        
        Case "Too"
            FormEvents = False
            ReceiveNO.value = ""
            BookNO.value = ""
            DateIN.value = ""
            From.value = ""
            Detail.value = ""
            Results.Clear
            FormEvents = True
            
        Case "Detail"
            FormEvents = False
            ReceiveNO.value = ""
            BookNO.value = ""
            DateIN.value = ""
            From.value = ""
            Too.value = ""
            Results.Clear
            FormEvents = True
            
        Case Else
            FormEvents = False
            ReceiveNO.value = ""
            BookNO.value = ""
            DateIN.value = ""
            From.value = ""
            Too.value = ""
            Detail.value = ""
            Results.Clear
            FormEvents = True
            
        End Select

End Sub

Private Sub BasicCalendar_Click()
    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then SearchForm.DateIN.Text = Format(dateVariable, "d mmm yyyy")
End Sub

Private Sub ClearBtn_Click()

    ClearForm ("")
    
End Sub

Private Sub CloseBtn_Click()
    
    Me.Hide

End Sub

Private Sub ReceiveNO_Change()
    
    If FormEvents Then ClearForm ("ReceiveNO")
    
End Sub

Private Sub BookNO_Change()
    
    If FormEvents Then ClearForm ("BookNO")
    
End Sub

Private Sub DateIN_Change()
    
    If FormEvents Then ClearForm ("DateIN")
    
End Sub

Private Sub From_Change()
    
    If FormEvents Then ClearForm ("From")
    
End Sub

Private Sub Too_Change()
    
    If FormEvents Then ClearForm ("Too")
    
End Sub

Private Sub Detail_Change()
    
    If FormEvents Then ClearForm ("Detail")
    
End Sub
Private Sub SearchBtn_Click()

    Dim SearchTerm As String
    Dim SearchColumn As String
    Dim RecordRange As Range
    Dim FirstAddress As String
    Dim FirstCell As Range
    Dim RowCount As Integer
    
    If ReceiveNO.value = "" And BookNO.value = "" And DateIN.value = "" And From.value = "" And Too.value = "" And Detail.value = "" Then
    
        MsgBox "No search term specified", vbCritical + vbOKOnly
        Exit Sub
    
    End If
  
    If ReceiveNO.value <> "" Then
    
        SearchTerm = ReceiveNO.value
        SearchColumn = "เลขทะเบียนรับ"
        
    End If
    
    If BookNO.value <> "" Then
    
        SearchTerm = BookNO.value
        SearchColumn = "ที่"
        
    End If

    If DateIN.value <> "" Then
    
        SearchTerm = DateIN.value
        SearchColumn = "ลงวันที่"
        
    End If

    If From.value <> "" Then
    
        SearchTerm = From.value
        SearchColumn = "จาก"
        
    End If
    
    If Too.value <> "" Then
    
        SearchTerm = Too.value
        SearchColumn = "ถึง"
        
    End If
    
    If Detail.value <> "" Then
    
        SearchTerm = Detail.value
        SearchColumn = "เรื่อง"
    End If 
    Results.Clear
        With Range("data[" & SearchColumn & "]")
            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
            If Not RecordRange Is Nothing Then
                FirstAddress = RecordRange.Address
                RowCount = 0
                Do
                    Set FirstCell = Range("B" & RecordRange.Row)
                    Results.AddItem
                    Results.List(RowCount, 0) = FirstCell(1, 1)
                    Results.List(RowCount, 1) = FirstCell(1, 2)
                    Results.List(RowCount, 2) = FirstCell(1, 3)
                    Results.List(RowCount, 3) = FirstCell(1, 4)
                    Results.List(RowCount, 4) = FirstCell(1, 5)
                    Results.List(RowCount, 5) = FirstCell(1, 6)
                    RowCount = RowCount + 1
                    Set RecordRange = .FindNext(RecordRange)
                    If RecordRange Is Nothing Then
                        Exit Sub
                    End If
                Loop While RecordRange.Address <> FirstAddress
            Else         
                Results.AddItem
                Results.List(RowCount, 0) = "ไม่พบข้อมูลที่ต้องการ!!!"        
            End If
        End With
End Sub

Private Sub UserForm_Initialize()

    FormEvents = True

End Sub

Code: Select all

Private Sub BasicCalendar_Click()
    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then SearchForm.DateIN.Text = Format(dateVariable, "d mmm yyyy")
End Sub

Code: Select all

Sub SearchData()

    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("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")
    
    
    iDatabaseRow = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row
    
    
    sColumn = frmForm.cmbSearchColumn.value
    
    sValue = frmForm.txtSearch.value
    
    
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:K1"), 0)
    
    'Remove filter from Database worksheet
    
    If shDatabase.FilterMode = True Then
    
       shDatabase.AutoFilterMode = False
    
    End If

    'Apply filter on Database worksheet
    
    If frmForm.cmbSearchColumn.value = "เลขทะเบียนรับ" Then
    
        shDatabase.Range("A1:K" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
    
    Else
    
        shDatabase.Range("A1:K" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    
    End If
    
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    
        'Code to remove the previous data from SearchData worksheet
        
        shSearchData.Cells.Clear
        
        shDatabase.AutoFilter.Range.Copy shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        
        
        frmForm.lstDatabase.ColumnCount = 12
        
        frmForm.lstDatabase.ColumnWidths = "40,70,75,75,75,80,70,70,70,350,70,70"
        
        If iSearchRow > 1 Then
        
            frmForm.lstDatabase.RowSource = "SearchData!A2:K" & iSearchRow
            
            MsgBox "ไม่พบข้อมูลที่ค้นหา"
        
        End If
        
        
    Else
    
       MsgBox "No record found."
    
    End If

    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True


End Sub

Code: Select all

'Other code
'Remove filter from Database worksheet
    
If shDatabase.FilterMode = True Then
    shDatabase.AutoFilterMode = False
    shDatabase.ShowAllData
End If

'Apply filter on Database worksheet

If frmForm.cmbSearchColumn.value = "เลขทะเบียนรับ" Then

    shDatabase.Range("A1:K" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue

Else

    shDatabase.Range("A1:K" & 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")
'Other code

Code: Select all

'Other code
If shDatabase.FilterMode Then
    shDatabase.AutoFilterMode = False
    'shDatabase.ShowAllData
    shDatabase.ListObjects("Table2").AutoFilter.ShowAllData
End If
'Other code