Read several threads and suggestions, still getting [Type mismatch (Error 13)]

B

badox

New Member
Joined
Dec 27, 2022
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
Rich (BB code): 
Option Explicit

Sub Reset()            <- F8 ERROR
  
    Dim iRow As Long
  
    iRow = [Counta (Database!A:A)] ' identifying the last row            <- F8 ERROR
  
    With frmForm
  
        .txtPVnr.Value = ""
        .txtDatum.Value = ""
        .txtVerdachte.Value = ""
        .txtBedrijf.Value = ""
      
        .cmbPost.Clear
        .cmbPost.AddItem "post1"
        .cmbPost.AddItem "post2"
        .cmbPost.AddItem "post3"
      
        .cmbLocatie.Clear
        .cmbLocatie.AddItem "locatie1"
        .cmbLocatie.AddItem "locatie2"
        .cmbLocatie.AddItem "locatie3"
      
        .optJa1.Value = False
        .optNee1.Value = False
      
        .cmbOvertreding.Clear
        .cmbOvertreding.AddItem "fout1"
        .cmbOvertreding.AddItem "fout2"
        .cmbOvertreding.AddItem "fout3"
              
        .txtGewicht.Value = ""
        .txtTelling.Value = ""
        .txtBoete.Value = ""
      
        .cmbBevinding.Clear
        .cmbBevinding.AddItem "overtreding1"
        .cmbBevinding.AddItem "overtreding2"
        .cmbBevinding.AddItem "overtreding3"
              
        .cmbOpslagplaats.Clear
        .cmbOpslagplaats.AddItem "opslag1"
        .cmbOpslagplaats.AddItem "opslag2"
        .cmbOpslagplaats.AddItem "opslag3"

        .cmbVerbalisant1.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
        .cmbVerbalisant2.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
        .cmbVerbalisant3.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
        .cmbVerbalisant4.Clear
        .cmbVerbalisant1.AddItem "person1"
        .cmbVerbalisant1.AddItem "person2"
        .cmbVerbalisant1.AddItem "person3"
      
      
        .optJa2.Value = False
        .optNee2.Value = False
      
        .txtHoofdkantoor.Value = ""
        .txtOM.Value = ""
        .txtOpmerking.Value = ""
      
        .lstDatabase.ColumnCount = 22
        .lstDatabase.ColumnHeads = True
      
        .lstDatabase.ColumnWidths = "30,30,30,60,60,50,50,30,75,40,40,40,75,75,75,75,75,75,30,40,40,150"
      
        If iRow > 1 Then
          
            .lstDatabase.RowSource = "Database!A2:U" & iRow
        Else
          
            .lstDatabase.RowSource = "Database!A2:U2"
        End If
      
    End With
  
End Sub

Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long
  
    Set sh = ThisWorkbook.Sheets("Database") 'assign the worksheet name to the defined worksheet variable Sh
  
    iRow = [Counta (Database!A:A)] + 1
  
    With sh
  
        .Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 2) = frmForm.txtPVnr.Value
        .Cells(iRow, 3) = frmForm.txtDatum.Value
        .Cells(iRow, 4) = frmForm.txtVerdachte.Value
        .Cells(iRow, 5) = frmForm.txtBedrijf.Value
        .Cells(iRow, 6) = frmForm.cmbPost.Value
        .Cells(iRow, 7) = frmForm.cmbLocatie.Value
        .Cells(iRow, 8) = IIf(frmForm.optJa1.Value = True, "Ja", "Nee")
        .Cells(iRow, 9) = frmForm.cmbOvertreding.Value
        .Cells(iRow, 10) = frmForm.txtGewicht.Value
        .Cells(iRow, 11) = frmForm.txtTelling.Value
        .Cells(iRow, 12) = frmForm.txtBoete.Value
        .Cells(iRow, 13) = frmForm.cmbBevinding.Value
        .Cells(iRow, 14) = frmForm.cmbVerbalisant1.Value
        .Cells(iRow, 15) = frmForm.cmbVerbalisant2.Value
        .Cells(iRow, 16) = frmForm.cmbVerbalisant3.Value
        .Cells(iRow, 17) = frmForm.cmbVerbalisant4.Value
        .Cells(iRow, 18) = frmForm.cmbOpslagplaats.Value
        .Cells(iRow, 19) = IIf(frmForm.optJa2.Value = True, "Ja", "Nee")
        .Cells(iRow, 20) = frmForm.txtHoofdkantoor.Value
        .Cells(iRow, 21) = frmForm.txtOM.Value
        .Cells(iRow, 22) = frmForm.txtOpmerking.Value
      
        End With
      
End Sub


Public Sub Show_Form()
  
    frmForm.Show            <- F8 ERROR
      
End Sub

______________________________________
Option Explicit

Private Sub cmdReset_Click()
  
    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want ot reset the form?", vbYesNo + vbInformation, "Confirmation")
  
    If msgValue = vbNo Then Exit Sub
  
    Call Reset
  
End Sub

Private Sub cmdSubmit_Click()
  
    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want ot save the data?", vbYesNo + vbInformation, "Confirmation")
  
    If msgValue = vbNo Then Exit Sub
  
    Call Submit
    Call Reset
  
End Sub


Private Sub UserForm_Initialize()            <- F8 ERROR

    Call Reset            <- F8 ERROR
  
End Sub
 
Sort by date Sort by votes
try setting your icolumn variable to the following:
VBA Code: 
    iColumn = ActiveCell.Column
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Thanks for the suggestion, it does eliminate the error, however the search comes back with no results and the edit function also doesn't work...
Maybe I bit off more than I can chew and included too many features in the code that conflict with eachother.
Candyman8019 said:
try setting your icolumn variable to the following:
VBA Code: 
    iColumn = ActiveCell.Column
Click to expand...

VBA Code: 
Sub Reset()
    
    Dim iRow As Long
    
    iRow = Sheets("Database").UsedRange.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With frmForm
    
        .txtPVnr.Value = ""
        .txtDatum.Value = ""
        .txtVerdachte.Value = ""
        .txtBedrijf.Value = ""
        
        .cmbPost.Clear
        .cmbPost.AddItem "post1"
        .cmbPost.AddItem "post2"
        .cmbPost.AddItem "post2"
        
        .cmbLocatie.Clear
        .cmbLocatie.AddItem "locatie1"
        .cmbLocatie.AddItem "locatie2"
        .cmbLocatie.AddItem "locatie3"
        
        .optJa1.Value = False
        .optNee1.Value = False
        
        .cmbOvertreding.Clear
        .cmbOvertreding.AddItem "overtreding1"
        .cmbOvertreding.AddItem "overtreding2"
        .cmbOvertreding.AddItem "overtreding3"
        
        .txtGewicht.Value = ""
        .txtTelling.Value = ""
        .txtBoete.Value = ""
        
        .txtRowNumber.Value = ""
        
        .cmbBevinding.Clear
        .cmbBevinding.AddItem "bevinding1"
        .cmbBevinding.AddItem "bevinding2"
        
        .cmbOpslagplaats.Clear
        .cmbOpslagplaats.AddItem "opslag1"
        .cmbOpslagplaats.AddItem "opslag2"
        
        .cmbVerbalisant1.Clear
        .cmbVerbalisant1.AddItem "verbal1"
        .cmbVerbalisant1.AddItem "verbal2"
        
        .cmbVerbalisant2.Clear
        .cmbVerbalisant2.AddItem "verbal1"
        .cmbVerbalisant2.AddItem "verbal2"
        
        .cmbVerbalisant3.Clear
        .cmbVerbalisant3.AddItem "verbal1"
        .cmbVerbalisant3.AddItem "verbal2"
        
        .cmbVerbalisant4.Clear
        .cmbVerbalisant4.AddItem "verbal1"
        .cmbVerbalisant4.AddItem "verbal2"
        
        .optJa2.Value = False
        .optNee2.Value = False
        
        .txtHoofdkantoor.Value = ""
        .txtOM.Value = ""
        .txtOpmerking.Value = ""
        
        'Below code are associated with Search Feature
        Call Add_SearchColumn
        ThisWorkbook.Sheets("Database").AutoFilterMode = False
        ThisWorkbook.Sheets("SearchData").AutoFilterMode = False
        ThisWorkbook.Sheets("SearchData").Cells.Clear
        '---------------------------------------------
        
        .lstDatabase.ColumnCount = 24
        .lstDatabase.ColumnHeads = True
        
        .lstDatabase.ColumnWidths = "25,50,50,85,85,60,75,60,150,60,60,60,100,100,90,90,90,90,70,60,50,500,90,90"
        
        If iRow > 1 Then
            
            'Collumn range to display in Database window-----
            .lstDatabase.RowSource = "Database!A2:X" & iRow
        Else
            
            .lstDatabase.RowSource = "Database!A2:X2"
        End If
            '------------------------------------------------
    End With
    
End Sub

Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long
    
    Set sh = ThisWorkbook.Sheets("Database") 'assign the worksheet name to the defined worksheet variable Sh
    
        If frmForm.txtRowNumber.Value = "" Then
        
            iRow = Sheets("Database").UsedRange.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Else
    
            iRow = frmForm.txtRowNumber.Value
    
        End If
            
    With sh
        
        'S.N.-----
        .Cells(iRow, 1) = iRow - 1
        '---------
        
        .Cells(iRow, 2) = frmForm.txtPVnr.Value
        .Cells(iRow, 3) = frmForm.txtDatum.Value
        .Cells(iRow, 4) = frmForm.txtVerdachte.Value
        .Cells(iRow, 5) = frmForm.txtBedrijf.Value
        '------
        .Cells(iRow, 6) = frmForm.cmbPost.Value
        .Cells(iRow, 7) = frmForm.cmbLocatie.Value
        '------
        .Cells(iRow, 8) = IIf(frmForm.optJa1.Value = True, "Ja", "Nee")
        '-----
        .Cells(iRow, 9) = frmForm.cmbOvertreding.Value
        '-----
        .Cells(iRow, 10) = frmForm.txtGewicht.Value
        .Cells(iRow, 11) = frmForm.txtTelling.Value
        .Cells(iRow, 12) = frmForm.txtBoete.Value
        '-----
        .Cells(iRow, 13) = frmForm.cmbBevinding.Value
        .Cells(iRow, 14) = frmForm.cmbOpslagplaats.Value
        .Cells(iRow, 15) = frmForm.cmbVerbalisant1.Value
        .Cells(iRow, 16) = frmForm.cmbVerbalisant2.Value
        .Cells(iRow, 17) = frmForm.cmbVerbalisant3.Value
        .Cells(iRow, 18) = frmForm.cmbVerbalisant4.Value
        '-----
        .Cells(iRow, 19) = IIf(frmForm.optJa2.Value = True, "Ja", "Nee")
        '-----
        .Cells(iRow, 20) = frmForm.txtHoofdkantoor.Value
        .Cells(iRow, 21) = frmForm.txtOM.Value
        .Cells(iRow, 22) = frmForm.txtOpmerking.Value
        
        'Register User and Date of entry or edit------
        .Cells(iRow, 23) = Application.UserName
        .Cells(iRow, 24) = [Text(now(),"DD-MM-YYYY HH:MM:SS")]
        '---------------------------------------------
        
        End With
        
End Sub


Sub Show_Form()
    
    frmForm.Show
        
End Sub


'Function to enable the selecting of rows in the Database
Function selected_List() As Long
    
    Dim i As Long
    
    selected_List = 0
    
    For i = 0 To frmForm.lstDatabase.ListCount - 1
        
        If frmForm.lstDatabase.Selected(i) = True Then
            
            selected_List = i + 1
            Exit For
            
        End If
        
    Next i
    
End Function


Sub Add_SearchColumn()

    frmForm.EnableEvents = False
    
    With frmForm.cmbSearchColumn
    
        .Clear
        
        .AddItem "All"
        
        'Search queries-----
        .AddItem "Datum"
        .AddItem "Verdachte(n)"
        .AddItem "Bedrijf"
        .AddItem "Post"
        .AddItem "Locatie"
        .AddItem "Aanhouding"
        .AddItem "Overtreding"
        .AddItem "Gewicht in Gr."
        .AddItem "Telling"
        .AddItem "Boete"
        .AddItem "Bevinding"
        .AddItem "Opslagplaats"
        .AddItem "1ste verbalisant"
        .AddItem "2de verbalisant"
        .AddItem "3de verbalisant"
        .AddItem "4de verbalisant"
        .AddItem "Opmerkingen"
        .AddItem "Submitted By"
        .AddItem "Submitted On"
        
        .Value = "All"
        
    End With
    
    frmForm.EnableEvents = True
    
    frmForm.txtSearch.Value = ""
    frmForm.txtSearch.Enabled = False
    frmForm.cmdSearch.Enabled = False

End Sub


Sub SearchData()
    
    Application.ScreenUpdating = False
    
    'Database sheet
    Dim shDatabase As Worksheet
    'SearchData sheet
    Dim shSearchData As Worksheet
    
    'To hold the selected column number in Database sheet
    Dim iColumn As Integer
    'To store the last non-blank row number available in Database sheet
    Dim iDatabaseRow As Long
    'To hold the last non-blank row number available in SeachData sheet
    Dim iSearchRow As Long
    
    'To store the column selection
    Dim sColumn As String
    'To hold the search text value
    Dim sValue As String
    
    Set shDatabase = ThisWorkbook.Sheets("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")
        
iDatabaseRow = Sheets("Database").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

        
    sColumn = frmForm.cmbSearchColumn.Value
    sValue = frmForm.txtSearch.Value
    
iColumn = ActiveCell.Column
            
    'Remove filter from Database worksheet
    If shDatabase.FilterMode = True Then
        
        shDatabase.AutoFilterMode = False
        
    End If
    
    'Apply filter on Database worksheet
    If frmForm.cmbSearchColumn.Value = "Datum" Then
        
        shDatabase.Range("A1:X" & iDatabaseRow).AutoFilter field:=iColumn, Criteria1:=sValue
        
    Else
        
        shDatabase.Range("A1:X" & 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
        shSearchData.AutoFilter.Range.Copy shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        frmForm.lstDatabase.ColumnCount = 24
        frmForm.lstDatabase.ColumnWidths = "25,50,50,85,85,60,75,60,150,60,60,60,100,100,90,90,90,90,70,60,50,500,90,90"
        
        If iSearchRow > 1 Then
        
            frmForm.lstDatabase.RowSource = "Searchdata!A2:X" & iSearchRow
            
            MsgBox "Records found."
        
        End If
        
    Else
    
        MsgBox "No record found."
        
    End If
    
    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub

VBA Code: 
Option Explicit
Public EnableEvents As Boolean

Private Sub cmbSearchColumn_Change()
    
    If Me.EnableEvents = False Then Exit Sub
    
    If Me.cmbSearchColumn.Value = "All" Then
    
        Call Reset
        
    Else
        
        Me.txtSearch.Value = ""
        Me.txtSearch.Enabled = True
        Me.cmdSearch.Enabled = True
        
    End If
    
End Sub

Private Sub cmdDelete_Click()
    
    Dim iRow As Long
    
    If selected_List = 0 Then
        
        'Code to check if a row was selected
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        Exit Sub
        
    End If
    
    Dim i As VbMsgBoxResult
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "Confirmation")
    If i = vbNo Then Exit Sub
    
    iRow = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
        
    ThisWorkbook.Sheets("Database").Rows(iRow).Delete
    
    Call Reset
    
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"

End Sub

Private Sub cmdEdit_Click()
    
    If selected_List = 0 Then
        
        'Code to check if a row was selected
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
        Exit Sub
        
    End If
    
    'Code to update the values to respective controls
    Dim sAanhouding As String
    Dim sPVBevinding As String
        
    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
    
    Me.txtPVnr.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 2)
    Me.txtDatum.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 3)
    Me.txtVerdachte.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 4)
    Me.txtBedrijf.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 5)
    
    Me.cmbPost.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 6)
    Me.cmbLocatie.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 7)
    
    sAanhouding = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 8)
    If sAanhouding = "Ja" Then
        Me.optJa1.Value = True
    Else
        Me.optNee1.Value = True
    End If
    
    Me.cmbOvertreding.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 9)
    
    Me.txtGewicht.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 10)
    Me.txtTelling.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 11)
    Me.txtBoete.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 12)
    
    Me.cmbBevinding.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 13)
    Me.cmbOpslagplaats.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 14)
    Me.cmbVerbalisant1.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 15)
    Me.cmbVerbalisant2.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 16)
    Me.cmbVerbalisant3.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 17)
    Me.cmbVerbalisant4.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 18)
    
    sPVBevinding = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 19)
    If sPVBevinding = "Ja" Then
        Me.optJa2.Value = True
    Else
        Me.optNee2.Value = True
    End If
    
    Me.txtHoofdkantoor.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 20)
    Me.txtOM.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 21)
    Me.txtOpmerking.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 22)
    
    MsgBox "Please make the required changes and click on 'Submit' button to update.", vbOKOnly + vbInformation, "Edit"
    
End Sub

Private Sub cmdReset_Click()
    
    Dim msgValue As VbMsgBoxResult
    
        msgValue = MsgBox("Do you want ot reset the form?", vbYesNo + vbInformation, "Confirmation")
    
        If msgValue = vbNo Then Exit Sub
    
    Call Reset
    
End Sub


Private Sub cmdSubmit_Click()
    
    'Validation before submitting data-----------------
        If Me.cmbPost.Value = "" Then
            MsgBox ("Please select a Post"), vbCritical
            Exit Sub
        End If
    
        If Me.cmbLocatie.Value = "" Then
            MsgBox ("Please select a Locatie"), vbCritical
            Exit Sub
        End If
    
        If Me.cmbOvertreding.Value = "" Then
            MsgBox ("Please select a overtreding"), vbCritical
            Exit Sub
        End If
    
        If Me.cmbBevinding.Value = "" Then
            MsgBox ("Please select a Bevinding"), vbCritical
            Exit Sub
        End If
    
        If Me.cmbOpslagplaats.Value = "" Then
            MsgBox ("Please select a opslagplaats"), vbCritical
            Exit Sub
        End If
    
        If Me.cmbVerbalisant1.Value = "" Then
            MsgBox ("Please select a verbalisant"), vbCritical
            Exit Sub
        End If
    
        If Me.txtOpmerking.Value = "" Then
            MsgBox ("Please write a remark"), vbCritical
            Exit Sub
        End If
    '------------------------------------------------
    
    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want ot save the data?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then Exit Sub
    
    Call Submit
    Call Reset
    
End Sub


Private Sub cmdSearch_Click()
    
    If Me.txtSearch.Value = "" Then
    
        MsgBox "Please enter the search value.", vbOKOnly + vbInformation, "Search"
        
    End If
    
    Call SearchData
    
End Sub


Private Sub UserForm_Initialize()

    Call Reset
    
End Sub
 
Upvote 0
Are you able to share a sample of your workbook…it would be easier to determine what piece of code isn’t working as expected.
 
Upvote 0
You can share it via onedrive or lockbox. Also if there’s any sensitive data in the workbook it would be good to mock up some fake data.
 
Upvote 0
Give something like this a try for your search;

VBA Code: 
Sub SearchData()
    
    Application.ScreenUpdating = False
    
    'Database sheet
    Dim shDatabase As Worksheet
    'SearchData sheet
    Dim shSearchData As Worksheet
    
    'To hold the selected column number in Database sheet
    Dim iColumn As Integer
    'To store the last non-blank row number available in Database sheet
    Dim iDatabaseRow As Long
    'To hold the last non-blank row number available in SeachData sheet
    Dim iSearchRow As Long
    
    'To store the column selection
    Dim sColumn As String
    'To hold the search text value
    Dim sValue As String
    
    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:X1"), 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 = "Datum" Then
        
        shDatabase.Range("A1:X" & iDatabaseRow).AutoFilter field:=iColumn, Criteria1:=sValue
        
    Else
        
        shDatabase.Range("A1:X" & 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 Destination:=shSearchData.Range("A1")
        
        Application.CutCopyMode = False
 '       iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        iSearchRow = Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
       
        frmForm.lstDatabase.ColumnCount = 24
        frmForm.lstDatabase.ColumnWidths = "25,50,50,85,85,60,75,60,150,60,60,60,100,100,90,90,90,90,70,60,50,500,90,90"
        
        If iSearchRow > 1 Then
        
            frmForm.lstDatabase.RowSource = "SearchData!A2:X" & iSearchRow
            
            MsgBox "Records found."
        
        End If
        
    Else
    
        MsgBox "No record found."
        
    End If
    
    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Candyman8019 said:
Give something like this a try for your search;

VBA Code: 
Sub SearchData()
   
    Application.ScreenUpdating = False
   
    'Database sheet
    Dim shDatabase As Worksheet
    'SearchData sheet
    Dim shSearchData As Worksheet
   
    'To hold the selected column number in Database sheet
    Dim iColumn As Integer
    'To store the last non-blank row number available in Database sheet
    Dim iDatabaseRow As Long
    'To hold the last non-blank row number available in SeachData sheet
    Dim iSearchRow As Long
   
    'To store the column selection
    Dim sColumn As String
    'To hold the search text value
    Dim sValue As String
   
    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:X1"), 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 = "Datum" Then
       
        shDatabase.Range("A1:X" & iDatabaseRow).AutoFilter field:=iColumn, Criteria1:=sValue
       
    Else
       
        shDatabase.Range("A1:X" & 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 Destination:=shSearchData.Range("A1")
       
        Application.CutCopyMode = False
 '       iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        iSearchRow = Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
      
        frmForm.lstDatabase.ColumnCount = 24
        frmForm.lstDatabase.ColumnWidths = "25,50,50,85,85,60,75,60,150,60,60,60,100,100,90,90,90,90,70,60,50,500,90,90"
       
        If iSearchRow > 1 Then
       
            frmForm.lstDatabase.RowSource = "SearchData!A2:X" & iSearchRow
           
            MsgBox "Records found."
       
        End If
       
    Else
   
        MsgBox "No record found."
       
    End If
   
    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub
Click to expand...

Wow... :eek:

You make it look so simple... or am I just incompetent :unsure:
 
Upvote 0
badox said:
Wow... :eek:

You make it look so simple... or am I just incompetent :unsure:
Click to expand...
This is already doing exactly what I intended it for, if possible...

I realize now that whenever I select a row and click on edit, it'll move all the data up one column in the form... (I'm still trying to figure this out seeing that I've pre-assigned all the columns)

could I get a tip on counting the listed results...
 
Upvote 0
badox said:
This is already doing exactly what I intended it for, if possible...

I realize now that whenever I select a row and click on edit, it'll move all the data up one column in the form... (I'm still trying to figure this out seeing that I've pre-assigned all the columns)

could I get a tip on counting the listed results...
Click to expand...
Code: 
Sub SearchData()
    
    Application.ScreenUpdating = False
    
    'Database sheet
    Dim shDatabase As Worksheet
    'SearchData sheet
    Dim shSearchData As Worksheet
    
    'To hold the selected column number in Database sheet
    Dim iColumn As Integer
    'To store the last non-blank row number available in Database sheet
    Dim iDatabaseRow As Long
    'To hold the last non-blank row number available in SeachData sheet
    Dim iSearchRow As Long
    'To count the found results
    Dim counter As Long
        
    'To store the column selection
    Dim sColumn As String
    'To hold the search text value
    Dim sValue As String
    
    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:X1"), 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 = "Datum" Then
        
        shDatabase.Range("A1:X" & iDatabaseRow).AutoFilter field:=iColumn, Criteria1:=sValue
        
    Else
        
        shDatabase.Range("A1:X" & 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 Destination:=shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        
        'iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        iSearchRow = Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
       
        frmForm.lstDatabase.ColumnCount = 24
        frmForm.lstDatabase.ColumnWidths = "25,50,50,85,85,60,75,60,150,60,60,60,100,100,90,90,90,90,70,60,50,500,90,90"
        
        If iSearchRow > 1 Then
                
        If Application.CountA(iSearchRow) > 0 Then
        
            frmForm.lstDatabase.RowSource = "SearchData!A2:X" & iSearchRow
                        
            'counts the number of rows non-empty Cells
            counter = counter + 1
            
            MsgBox "Records found." & counter
        
        End If
        End If
    Else
    
        MsgBox "No record found."
        
    End If
    
    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub

I managed to solve the misalignment when pulling back up the data to edit it...
But counting the total found results still eludes me...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
User Form not Loading
Replies
5
Views
177
StevieMP
S
A
  • Question Question
Run-time error '380' Could not set the Value property. Invalid property value
Replies
7
Views
898
aabrazil1
A
A
  • Question Question
Autofilter code based on username
Replies
1
Views
374
aabrazil1
A
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
406
Peter_SSs
Peter_SSs
A
  • Question Question
Cannot enter text in textbox on form
Replies
3
Views
281
Anthony47
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top