VBA : run time error "13", type : mismatch-CodePudding

There's a mismatch type in my vba (IF statement part) code but I couldn't figured out, could someone explain me ?

Find below the related vba code.

Private Sub continue_entry_Click()

Dim row_entitee As Long

row_entitee = ThisWorkbook.Sheets("TABLE_ENTITEE").Range("A" & Rows.Count).End(xlUp).Row   1

ThisWorkbook.Sheets("TABLE_ENTITEE").Range("A" & row_entitee) = manual_entry.zone_entry.Value
ThisWorkbook.Sheets("TABLE_ENTITEE").Range("B" & row_entitee) = manual_entry.country_entry.Value
ThisWorkbook.Sheets("TABLE_ENTITEE").Range("C" & row_entitee) = manual_entry.entity_entry.Value
ThisWorkbook.Sheets("TABLE_ENTITEE").Range("D" & row_entitee) = manual_entry.code_entry.Value
ThisWorkbook.Sheets("TABLE_ENTITEE").Range("E" & row_entitee) = manual_entry.activity_entry.Value
ThisWorkbook.Sheets("TABLE_ENTITEE").Range("F" & row_entitee) = manual_entry.currency_entry.Value

entity_selection.entity_list.Value = manual_entry.entity_entry.Value

If ThisWorkbook.Sheets("TABLE_ENTITEE").Range("A" & row_entitee).Value And _
    ThisWorkbook.Sheets("TABLE_ENTITEE").Range("B" & row_entitee).Value And _
    ThisWorkbook.Sheets("TABLE_ENTITEE").Range("C" & row_entitee).Value And _
    ThisWorkbook.Sheets("TABLE_ENTITEE").Range("D" & row_entitee).Value And _
    ThisWorkbook.Sheets("TABLE_ENTITEE").Range("E" & row_entitee).Value And _
    ThisWorkbook.Sheets("TABLE_ENTITEE").Range("F" & row_entitee).Value Is Empty _
    Then MsgBox "All the field must be filled" Else _
    manual_entry.Hide: entity_selection.Show
End Sub

Thanks for your help and wishe you a very nice day.

Breat

I tried to make every field of my userform to be filled otherwise a msgbox appear.

CodePudding user response：

I think FreeFlow has identified the direct cause of the error message you are getting. I've added that recommendation and simplified your code using an object variable to refer to your worksheet and given your "IF" statement standard formatting.

Private Sub continue_entry_Click()

    Dim row_entitee As Long
    Dim s As Worksheet
    
    Set s = ThisWorkbook.Sheets("TABLE_ENTITEE")
    
    row_entitee = s.Range("A" & Rows.Count).End(xlUp).Row   1
    
    s.Cells(row_entitee, "A") = manual_entry.zone_entry.Value
    s.Cells(row_entitee, "B") = manual_entry.country_entry.Value
    s.Cells(row_entitee, "C") = manual_entry.entity_entry.Value
    s.Cells(row_entitee, "D") = manual_entry.code_entry.Value
    s.Cells(row_entitee, "E") = manual_entry.activity_entry.Value
    s.Cells(row_entitee, "F") = manual_entry.currency_entry.Value
    
    entity_selection.entity_list.Value = manual_entry.entity_entry.Value
    
    If s.Cells(row_entitee, "A").Value And _
       s.Cells(row_entitee, "B").Value And _
       s.Cells(row_entitee, "C").Value And _
       s.Cells(row_entitee, "D").Value And _
       s.Cells(row_entitee, "E").Value And _
       IsEmpty(s.Cells(row_entitee, "F").Value) Then 
        MsgBox "All the fields must be filled"
    Else
        manual_entry.Hide
        entity_selection.Show
    End If

End Sub

CodePudding user response：

In this answer, I'm showing you how I go about data validation on a form. Before writing to the sheet, I check the values in the form. Only if all the data validation checks pass, then will I write to the worksheet.

In this approach, I start with a variable named "message" as an empty string (the starting values for all string variables). Then I check each entry on the form to see if something has been entered. If not, I append to the message variable.

After all data validation checks, if the message variable is still empty, then I process the data, otherwise, I use the message variable to display to the user what the problem is. This code assumes that the controls on the form you are checking are textboxes, comboboxes, or listboxes.

Private Sub continue_entry_Click()

    Dim row_entitee As Long
    Dim s As Worksheet
    Dim message As String
    
    'data validation checks
    If Len(manual_entry.zone_entry.Value) = 0 Then message = message & ", zone"
    If Len(manual_entry.country_entry.Value) = 0 Then message = message & ", country"
    If Len(manual_entry.entity_entry.Value) = 0 Then message = message & ", entity"
    If Len(manual_entry.zone_entry.code_entry) = 0 Then message = message & ", code"
    If Len(manual_entry.activity_entry.Value) = 0 Then message = message & ", activity"
    If Len(manual_entry.currency_entry.Value) = 0 Then message = message & ", currency"
    
    If Len(message) = 0 Then
         ' all data validation checks pass, the message is still an empty string, 
         ' we are ready to proceed
         
        Set s = ThisWorkbook.Sheets("TABLE_ENTITEE")
        row_entitee = s.Range("A" & Rows.Count).End(xlUp).Row   1
        
        s.Cells(row_entitee, "A") = manual_entry.zone_entry.Value
        s.Cells(row_entitee, "B") = manual_entry.country_entry.Value
        s.Cells(row_entitee, "C") = manual_entry.entity_entry.Value
        s.Cells(row_entitee, "D") = manual_entry.code_entry.Value
        s.Cells(row_entitee, "E") = manual_entry.activity_entry.Value
        s.Cells(row_entitee, "F") = manual_entry.currency_entry.Value
        
        entity_selection.entity_list.Value = manual_entry.entity_entry.Value
         
        manual_entry.Hide
        entity_selection.Show
    Else
      ' at least one one data validation rule failed
      message = message   "All fields are required, please check the following:" & Mid(message, 2)
      MsgBox message, vbCritical
    End If
    

End Sub

I have not executed this code because I don't have the forms you are referencing, so there may be some debugging necessary ;-)

CodePudding user response：

(a) As already mentioned, the syntax for checking needs to be IsEmpty(cell). If you use cell is Empty, you will get an error 424 (object required)

(b) Your type mismatch comes from If Cells(row_entitee, "A").Value And... - this will force VBA to convert the content of the cell into a boolean. This will fail for string values (and give unwanted results for numeric values).

(c) I assume that you want to warn if any of the values is empty. In that case you need to use Or instead of And.

(d) IMHO, it's better to check the input before you write it back to the sheet, but that's a decision you need to make.

(e) Your usage of If - Else - is odd. Don't concatenate the statements for the If and the Else-branch and separate them with : (My advice: Don't use : at all)

Private Sub continue_entry_Click()

    Dim ws As Worksheet, row_entitee As Long
    Set ws = ThisWorkbook.Sheets("TABLE_ENTITEE")
    row_entitee = ws.Range("A" & ws.Rows.Count).End(xlUp).row   1

    ws.Range("A" & row_entitee) = manual_entry.zone_entry.Value
    ws.Range("B" & row_entitee) = manual_entry.country_entry.Value
    ws.Range("C" & row_entitee) = manual_entry.entity_entry.Value
    ws.Range("D" & row_entitee) = manual_entry.code_entry.Value
    ws.Range("E" & row_entitee) = manual_entry.activity_entry.Value
    ws.Range("F" & row_entitee) = manual_entry.currency_entry.Value

    entity_selection.entity_list.Value = manual_entry.entity_entry.Value

    If IsEmpty(ws.Range("A" & row_entitee).Value) _
    Or IsEmpty(ws.Range("B" & row_entitee).Value) _
    Or IsEmpty(ws.Range("C" & row_entitee).Value) _
    Or IsEmpty(ws.Range("D" & row_entitee).Value) _
    Or IsEmpty(ws.Range("E" & row_entitee).Value) _
    Or IsEmpty(ws.Range("F" & row_entitee).Value) Then
        MsgBox "All the field must be filled"
    Else
        manual_entry.Hide
        entity_selection.Show
    End If
End Sub