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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
To get the last row, try something like:
VBA Code: 
sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
 
Upvote 0
Candyman8019 said:
To get the last row, try something like:
VBA Code: 
sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Click to expand...
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
Candyman8019 said:
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.
Click to expand...
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
Candyman8019 said:
Use the same syntax as you did for lrow.
VBA Code: 
idatabaserow = sheets("Database").usedrange.columns(1).find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Click to expand...
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
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... -_-'

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

VBA Code: 
shDatabase.Range("A1:X" & iDatabaseRow).AutoFilter field:=iColumn, Criteria1:="*" & sValue & "*"
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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