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



## badox (Dec 29, 2022)

```
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
```


----------



## Candyman8019 (Dec 29, 2022)

To get the last row, try something like:

```
sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
```


----------



## badox (Dec 29, 2022)

Candyman8019 said:


> To get the last row, try something like:
> 
> ```
> sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
> ```


Hi, 
Thanks for your reply, for clarity, am I supposed to remove:


```
Dim iRow As Long
  
    iRow = [Counta (Database!A:A)] ' identifying the last row
```

and replace it with your code?

Are there any other errors in my code that prohibit the form from displaying?
I don't know if this is relevant, but the database has information in it already and is currently being used, I'm trying to upgrade it so that the data entry can be simplified.


----------



## Candyman8019 (Dec 29, 2022)

It will be a replace...

```
iRow = sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
```

Give that a try and see if/where the next error shows up.


----------



## badox (Jan 1, 2023)

Candyman8019 said:


> It will be a replace...
> 
> ```
> iRow = sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
> ...


I kept expanding the form while using your code and to see if it would keep up and you saved my life...
The form is working perfectly

Happy New Year and best wishes to you


----------



## Candyman8019 (Jan 1, 2023)

Right on. Happy new year to you as well!


----------



## badox (Jan 1, 2023)

I think I just messed something up, tried adding a search function to the form and I assume there is a conflict with the code I'm using, but I can't figure out why it's suddenly giving this error.


```
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(x1Up).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 = "P.V. nr." 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(x1Up).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
```


----------



## Candyman8019 (Jan 1, 2023)

Use the same syntax as you did for lrow.  

```
idatabaserow = sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
```


----------



## badox (Jan 1, 2023)

Candyman8019 said:


> Use the same syntax as you did for lrow.
> 
> ```
> idatabaserow = sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
> ```


Thanks for the upgrade, now it seems that the rest of my original code is incompatible, or maybe it already was but wasn't showing

This is now showing the 1004 error after I used your improvement, your code is a little too advanced for my to try and re-write my own to fit it... -_-'


```
iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:X1"), 0)
```


----------



## badox (Jan 1, 2023)

badox said:


> Thanks for the upgrade, now it seems that the rest of my original code is incompatible, or maybe it already was but wasn't showing
> 
> This is now showing the 1004 error after I used your improvement, your code is a little too advanced for my to try and re-write my own to fit it... -_-'
> 
> ...


Sorry I copied the wrong code...


```
shDatabase.Range("A1:X" & iDatabaseRow).AutoFilter field:=iColumn, Criteria1:="*" & sValue & "*"
```


----------



## badox (Dec 29, 2022)

```
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
```


----------



## Candyman8019 (Jan 2, 2023)

try setting your icolumn variable to the following:

```
iColumn = ActiveCell.Column
```


----------



## badox (Jan 2, 2023)

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:
> 
> ```
> iColumn = ActiveCell.Column
> ```




```
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
```


```
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
```


----------



## Candyman8019 (Jan 2, 2023)

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.


----------



## badox (Jan 2, 2023)

Candyman8019 said:


> 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.


sure, how do I share it?
The upload mini-sheet?


----------



## Candyman8019 (Jan 2, 2023)

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.


----------



## badox (Jan 2, 2023)

Candyman8019 said:


> 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.











						DataEntry testForm.xlsm
					

Microsoft Excel Workbook



					1drv.ms


----------



## Candyman8019 (Jan 2, 2023)

Give something like this a try for your search;


```
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
```


----------



## badox (Jan 3, 2023)

Candyman8019 said:


> Give something like this a try for your search;
> 
> 
> ```
> ...



Wow... 

You make it look so simple... or am I just incompetent


----------



## badox (Jan 3, 2023)

badox said:


> Wow...
> 
> You make it look so simple... or am I just incompetent


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...


----------



## badox (Jan 3, 2023)

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...




```
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...


----------



## badox (Dec 29, 2022)

```
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
```


----------



## Candyman8019 (Jan 3, 2023)

Since you know how to get the last line on the SearchData tab, and you have 1 header row, the result count would be:


```
Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row -1
```


----------



## badox (Jan 3, 2023)

Candyman8019 said:


> Since you know how to get the last line on the SearchData tab, and you have 1 header row, the result count would be:
> 
> 
> ```
> ...


... no that just removes one of the search results and still displays the total as being only 1


----------



## Candyman8019 (Jan 3, 2023)

Where are you trying to display the result count?


----------



## badox (Jan 3, 2023)

Candyman8019 said:


> Where are you trying to display the result count?


in the message that post that records were found.


```
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 'Display total records found with search
```


----------



## Candyman8019 (Jan 3, 2023)

ah, okay.  In that case change this:


```
MsgBox "Records found."
```

to this


```
MsgBox iSearchRow - 1 & " Records found."
```


----------



## badox (Jan 4, 2023)

Candyman8019 said:


> ah, okay.  In that case change this:
> 
> 
> ```
> ...


Many thanks, this solved the problem entirely, just had to also change back, we're finally cooking with gas...



```
iSearchRow = Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row -1
```

to


```
iSearchRow = Sheets("SearchData").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
```


----------



## Candyman8019 (Jan 4, 2023)

There’s no better way to cook!


----------



## badox (Jan 4, 2023)

I just realized that this thread did deviate from the original error, I need to go and mark the response as solved.

I'm still learning and exploring the various capabilities of vba and trying to improve my understanding and application of it.

I thought it would be more practical to have the form insert a new row above the data submitted so that I always have the most recent data at the top, the code isn't returning any errors, however it's also not doing what I wanted it to.


```
Sub NewLine()
        
        On Error GoTo enditall
            Application.EnableEvents = False
            
            If Target.Cells.Column = 24 Then
                
                'If data is stored in row 2 move one row down
                If Target.Cells.Row = 2 Then
                    
                    N = Target.Cells.Row
                    If Range("X" & N).Value <> "" Then
                    
                    Range("X2").EntireRow.Insert
                    Range("A2").Select
                    
                End If
            End If
        End If
        
enditall:
        Application.EnableEvents = True
        
End Sub
```


----------



## Candyman8019 (Jan 4, 2023)

What sheet are you wanting to insert a row on?
It could be as simple as:

```
sheets(“searchdata”).range(“a2”).entirerow.insert
```


----------



## badox (Jan 4, 2023)

Candyman8019 said:


> What sheet are you wanting to insert a row on?
> It could be as simple as:
> 
> ```
> ...


on the database sheet,

The idea is to always have the most recent input data to the top of the Database

3. 03-01-2023
2. 02-01-2023
1. 01-01-2023


----------



## badox (Dec 29, 2022)

```
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
```


----------



## Candyman8019 (Jan 4, 2023)

So then. 


```
sheets(“database”).range(“a2”).entirerow.insert
```


----------



## badox (Jan 4, 2023)

Candyman8019 said:


> So then.
> 
> 
> ```
> ...


I'm unsure where I'd place this line, I tried adding it separate in order not to mix-up the existing code


```
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 NewLine()
    
    Dim sh As Worksheet
    
    Set sh = ThisWorkbook.Sheets("Database")
        
 sh.Range(“A2”).EntireRow.Insert
 
End Sub
```


----------



## Candyman8019 (Jan 4, 2023)

Replace the first section of your submit routine with this:

```
Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long
       Sheet2.Range("A2").EntireRow.Insert
       Sheet2.Range("A3").EntireRow.Copy
       Sheet2.Range("A2").EntireRow.PasteSpecial Paste:=xlPasteFormats
    
    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
             iRow = Sheets("Database").Range("A2").Row
  '      Else
    
  '          iRow = frmForm.txtRowNumber.Value
    
  '      End If
            
    With sh
```


----------



## badox (Jan 4, 2023)

Candyman8019 said:


> Replace the first section of your submit routine with this:
> 
> ```
> Sub Submit()
> ...


it works, however the serial nr. in the ("s.n.") at the front stays at 1 and doesn't count up


----------



## badox (Thursday at 7:06 AM)

badox said:


> it works, however the serial nr. in the ("s.n.") at the front stays at 1 and doesn't count up


I've been trying to combine what you suggested and my obsolete code, I understand how your code is constructed, however I also need the following lines else the serial number doesn't count and forever stays at 1

```
'iRow = Sheets("Database").UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
iRow = Sheets("Database").Range("A2").Row
```

This line is for the search feature

```
frmForm.txtRowNumber.Value
```

This line is what determines the serial number

```
.UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
iRow = Sheets("Database").Range("A2").Row
```


----------



## Candyman8019 (Thursday at 7:59 AM)

make this change for the serial number section:

```
'S.N.-----
        '.Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 1) = Application.WorksheetFunction.Max(Sheets("Database").Range("a:a")) + 1
        '---------
```


----------



## badox (Thursday at 12:01 PM)

Candyman8019 said:


> make this change for the serial number section:
> 
> ```
> 'S.N.-----
> ...


Great it does add the s.n. to the top together with the submitted data.
But now it saves any row I selected to edit as a new row, with the previous and new information... It doesn't save over the row that was selected.


----------



## Candyman8019 (Thursday at 6:13 PM)

I think the best route to go here is to back up to where you had things working with adding new records at the bottom.  We know it's stable from there.  Then, at the bottom of your Reset() sub, add the following to sort the data.

```
With ActiveWorkbook.Worksheets("Database").Sort
        .SetRange Range("A1:X500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
```

I feel like this will be a cleaner approach than trying to insert lines, copy formats, etc.


----------



## badox (Thursday at 7:34 PM)

Candyman8019 said:


> I think the best route to go here is to back up to where you had things working with adding new records at the bottom.  We know it's stable from there.  Then, at the bottom of your Reset() sub, add the following to sort the data.
> 
> ```
> With ActiveWorkbook.Worksheets("Database").Sort
> ...


It still saves the data to the bottom of the table and not to the top...

If it weren't for the need to edit the information, your earlier suggestion was perfect.
but I'm leaning more to my code being obsolete...


----------



## Candyman8019 (Thursday at 8:01 PM)

Correct. It will save the data to the bottom, and then you add that snippet of code to the end of your Reset() code and after adding the row it will sort the sheet ascending by SN


----------



## badox (Dec 29, 2022)

```
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
```


----------



## badox (Thursday at 9:29 PM)

```
Sub Reset()

    With ActiveWorkbook.Worksheets("Database").Sort
        .SetRange Range("A1:X500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Dim iRow As Long
```

or


```
End With
    
        With ActiveWorkbook.Worksheets("Database").Sort
        .SetRange Range("A1:X500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
```

I've tried both but they give the same result


----------



## Candyman8019 (Thursday at 9:31 PM)

And what was the result?   You’ll want it at the end of your reset routine.


----------



## badox (Friday at 5:32 AM)

It still only saves the new records to the end of the table. 

I might have to rewrite the whole edit and submit sections to try and accommodate the earlier code you gave, that one worked perfectly. 

It only seemed to loose the link to the row in the table when submitting it back after edit and saved it as a new row entirely.


----------



## badox (Friday at 6:05 AM)

badox said:


> It still only saves the new records to the end of the table.
> 
> I might have to rewrite the whole edit and submit sections to try and accommodate the earlier code you gave, that one worked perfectly.
> 
> It only seemed to loose the link to the row in the table when submitting it back after edit and saved it as a new row entirely


when I look it all over again... I used this line to link the select and edit the values, so somehow I have to find a way to maintain this throughout your earlier edit and it should resolve both problems...


```
Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
```


```
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
   
    'Display info exactly as is
    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
```


----------



## Candyman8019 (Friday at 7:57 AM)

Can you send me a copy of your latest workbook with all of the changes so far?


----------



## badox (Friday at 9:21 AM)

Candyman8019 said:


> Replace the first section of your submit routine with this:
> 
> ```
> Sub Submit()
> ...


I'm experimenting with the


> Sheet2.Range("A3").EntireRow.Copy


Because it just dawned on me that the row that needs to be copied is the *LastRow* not row A3, because as data is put into the sheet the row number will keep increasing


----------



## badox (Friday at 10:13 AM)

Candyman8019 said:


> Can you send me a copy of your latest workbook with all of the changes so far?


DataEntry testForm.xlsm


----------



## badox (Saturday at 10:25 PM)

badox said:


> DataEntry testForm.xlsm


I'm still experimenting with finding the last row, I think that was successful. 

But I'm stuck at finding how to collect that last row and paste it at the top.


----------



## Candyman8019 (Sunday at 11:20 AM)

Okay, give this a try...I think it'll be close.  Something is odd with editing an existing record though...take a look.  I have a few errands to run before I can look at it again.









						DataEntry testForm.xlsm
					

Microsoft Excel Workbook



					1drv.ms


----------



## badox (Sunday at 11:50 AM)

Candyman8019 said:


> Okay, give this a try...I think it'll be close.  Something is odd with editing an existing record though...take a look.  I have a few errands to run before I can look at it again.
> 
> 
> 
> ...


Thank you, and as you've noticed, there is a error in my code to edit existing records yes, it still saves them as a new record...


----------



## badox (Dec 29, 2022)

```
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
```


----------



## badox (Sunday at 12:37 PM)

badox said:


> Thank you, and as you've noticed, there is a error in my code to edit existing records yes, it still saves them as a new record...


Trying to think outside the box...

Maybe I can just leave the form as is and attempt to sort the results descending based on the serial numbers...

I'll have to expriment with the code to see how it functions first.


----------



## Candyman8019 (Sunday at 2:17 PM)

Thats exactly what I did if you download the link I posted above.  After the save, it sorts by serial number.


----------



## badox (Sunday at 4:03 PM)

Candyman8019 said:


> Thats exactly what I did if you download the link I posted above.  After the save, it sorts by serial number.


The link got corrupted because the file had the same name as the one I was experimenting with and now it's giving the same problem when I try editing a row... it just creates a new row with the same information


----------



## Candyman8019 (Sunday at 4:12 PM)

Here’s a link with it renamed. DataEntry testForm2.xlsm


----------



## badox (Sunday at 4:23 PM)

Candyman8019 said:


> Here’s a link with it renamed. DataEntry testForm2.xlsm


Yes I received it, it does as you say, sorts them by the s.n.
I still need to figure out why the edit function keeps saving the records as a new record though, maybe I made a mistake in the code


----------



## Candyman8019 (Sunday at 5:34 PM)

Check out this latest sheet.  I have sorted out the error with editing. I believe it should do what you're looking for now.  NOTE:  the code is a mess, so it needs to be cleaned up, ie remove old commented code, adjust indents, etc.









						DataEntry testForm3.xlsm
					

Microsoft Excel Workbook



					1drv.ms


----------



## badox (Sunday at 8:03 PM)

Candyman8019 said:


> Check out this latest sheet.  I have sorted out the error with editing. I believe it should do what you're looking for now.  NOTE:  the code is a mess, so it needs to be cleaned up, ie remove old commented code, adjust indents, etc.
> 
> 
> 
> ...


Works like a charm


----------

