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

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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
To get the last row, try something like:
VBA Code:
sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
 
Upvote 0
To get the last row, try something like:
VBA Code:
sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Hi,
Thanks for your reply, for clarity, am I supposed to remove:

VBA Code:
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.
 
Upvote 0
It will be a replace...
VBA Code:
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.
 
Upvote 0
Solution
It will be a replace...
VBA Code:
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.
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
 
Upvote 0
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.

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

2023-01-01.png
 
Upvote 0
Use the same syntax as you did for lrow.
VBA Code:
idatabaserow = sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
 
Upvote 0
Use the same syntax as you did for lrow.
VBA Code:
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... -_-'

VBA Code:
iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:X1"), 0)
 
Upvote 0
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... -_-'

VBA Code:
iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:X1"), 0)
Sorry I copied the wrong code...

VBA Code:
shDatabase.Range("A1:X" & iDatabaseRow).AutoFilter field:=iColumn, Criteria1:="*" & sValue & "*"
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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