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

B

badox

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

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

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

Sub Submit()

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


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

______________________________________
Option Explicit

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

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


Private Sub UserForm_Initialize()            <- F8 ERROR

    Call Reset            <- F8 ERROR
  
End Sub
 
Sort by date Sort by votes
VBA Code: 
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

VBA Code: 
    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
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
And what was the result? You’ll want it at the end of your reset routine.
 
Upvote 0
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.
 
Upvote 0
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
Click to expand...
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...

VBA Code: 
Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
VBA Code: 
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)
 
Upvote 0
Candyman8019 said:
Replace the first section of your submit routine with this:
VBA Code: 
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
Click to expand...
I'm experimenting with the
Sheet2.Range("A3").EntireRow.Copy
Click to expand...
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
 
Upvote 0
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 for IPAM.xlsm

1drv.ms 1drv.ms
 
Upvote 0
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.

DataEntry for IPAM.xlsm

1drv.ms 1drv.ms
Click to expand...
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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