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

VBA Code: 
 sheets(“database”).range(“a2”).entirerow.insert
 
Upvote 0

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.
Candyman8019 said:
So then.

VBA Code: 
 sheets(“database”).range(“a2”).entirerow.insert
Click to expand...
I'm unsure where I'd place this line, I tried adding it separate in order not to mix-up the existing code

VBA 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
 
Upvote 0
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
 
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...
it works, however the serial nr. in the ("s.n.") at the front stays at 1 and doesn't count up
 
Upvote 0
badox said:
it works, however the serial nr. in the ("s.n.") at the front stays at 1 and doesn't count up
Click to expand...
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
VBA Code: 
'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
VBA Code: 
frmForm.txtRowNumber.Value

This line is what determines the serial number
VBA Code: 
.UsedRange.Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
iRow = Sheets("Database").Range("A2").Row
 
Upvote 0
make this change for the serial number section:
VBA Code: 
        'S.N.-----
        '.Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 1) = Application.WorksheetFunction.Max(Sheets("Database").Range("a:a")) + 1
        '---------
 
Upvote 0
Candyman8019 said:
make this change for the serial number section:
VBA Code: 
        'S.N.-----
        '.Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 1) = Application.WorksheetFunction.Max(Sheets("Database").Range("a:a")) + 1
        '---------
Click to expand...
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.
 
Upvote 0
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.
VBA Code: 
    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.
 
Upvote 0
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.
VBA Code: 
    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.
Click to expand...
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... :cry:
 
Upvote 0
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
 
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,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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