Excel 2016, VBA, Run-time error 13: type mismatch error-CodePudding

I have created a UserForm with some textboxes and comboboxes inside, the data that is typed in textboxes are supposed to be inserted in cell in the sheet, the determation of which cell is based on the value in ComboBox4. unfortunately the code keeps giving me error: "Run-time error '13': Type mismatch" and I have not been able to find out what is going wrong?

if someone knows what the problem is please let me know.

Private Sub UserForm_Initialize()
    ComboBox3.List = [ADMIN!e2:E1000].Value
    ComboBox4.List = [PRODUCTION!O6:O1000].Value
End Sub

Private Sub ACCEPTBUTTON_Click()

Application.ScreenUpdating = False

Worksheets("PRODUCTION").Activate

Dim C As Long
For C = 1000 To 1 Step -1
If Cells(C   1, 1) Like ComboBox4 Then

Cells(C   1, 1).EntireRow.Select
Selection.EntireRow.Hidden = False
Application.CutCopyMode = False

End If
Next C

Range("AC" & (ActiveCell.Row)).Value = TextBox1.Value
Range("AD" & (ActiveCell.Row)).Value = TextBox2.Value
Range("AE" & (ActiveCell.Row)).Value = TextBox3.Value
Range("AF" & (ActiveCell.Row)).Value = TextBox4.Value
Range("AG" & (ActiveCell.Row)).Value = TextBox5.Value
Range("AH" & (ActiveCell.Row)).Value = TextBox6.Value
Range("AI" & (ActiveCell.Row)).Value = TextBox7.Value
Range("AJ" & (ActiveCell.Row)).Value = TextBox8.Value

ActiveCell.EntireRow.RowHeight = 16

Unload Me
Application.ScreenUpdating = True

End Sub

CodePudding user response：

Here's some commented code that should work for you. I did find it strange that you populate the values in ComboBox4 from column O, but then search column A for matches, is that intentional? (In the provided code, it searches for matches from the same list as populated the combobox which will guarantee a match is found).

Also, instead of a 1000 long loop to find the matches, this uses a Range.Find loop to increase speed and efficiency.

'Declare userform variables that any of this userform's Subs can reference
Private wb As Workbook
Private wsAdm As Worksheet
Private wsPrd As Worksheet
Private rAdmList As Range
Private rPrdList As Range

Private Sub UserForm_Initialize()
    
    'Populate userform variables
    Set wb = ThisWorkbook
    Set wsAdm = wb.Worksheets("ADMIN")
    Set wsPrd = wb.Worksheets("PRODUCTION")
    Set rAdmList = wsAdm.Range("E2", wsAdm.Cells(wsAdm.Rows.Count, "E").End(xlUp))  'Dynamically size list
    Set rPrdList = wsPrd.Range("O6", wsPrd.Cells(wsPrd.Rows.Count, "O").End(xlUp))  'Dynamically size list
    
    Me.ComboBox3.List = rAdmList.Value
    Me.ComboBox4.List = rPrdList.Value
    
End Sub

Private Sub ACCEPTBUTTON_Click()
    
    'Check if anything is selected from ComboBox4
    If Me.ComboBox4.ListIndex = -1 Then
        Me.ComboBox4.SetFocus
        MsgBox "Must select a Production item"
        Exit Sub
    End If
    
    'An item from the production list in combobox4 has been confirmed to be selected
    'Search the corresonding ComboBox4 list range to find the corresponding row
    '(In your original code, you are searching column A instead of the column that populated the combobox which is column O, is there a reason for that?)
    Dim rFound As Range, sFirst As String
    Set rFound = rPrdList.Find(Me.ComboBox4.Text, rPrdList(rPrdList.Cells.Count), xlValues, xlWhole)
    If Not rFound Is Nothing Then
        sFirst = rFound.Address 'Record first address of found item
        Do
            'Matching row found, unhide and populate cells with textbox values
            'Note that there is currently no check or validation that the textboxes are populated
            rFound.EntireRow.Hidden = False
            wsPrd.Cells(rFound.Row, "AC").Value = Me.TextBox1.Text
            wsPrd.Cells(rFound.Row, "AD").Value = Me.TextBox2.Text
            wsPrd.Cells(rFound.Row, "AE").Value = Me.TextBox3.Text
            wsPrd.Cells(rFound.Row, "AF").Value = Me.TextBox4.Text
            wsPrd.Cells(rFound.Row, "AG").Value = Me.TextBox5.Text
            wsPrd.Cells(rFound.Row, "AH").Value = Me.TextBox6.Text
            wsPrd.Cells(rFound.Row, "AI").Value = Me.TextBox7.Text
            wsPrd.Cells(rFound.Row, "AJ").Value = Me.TextBox8.Text
            
            'Search for next cell that matches
            Set rFound = rPrdList.FindNext(rFound)
        Loop While rFound.Address <> sFirst 'Loop until back at first address
    Else
        'If the item wasn't found, it's because the user manually typed in something in the combobox, or other error occurred
        Me.ComboBox4.SetFocus
        MsgBox "Invalid value entered for Production item"
        Exit Sub
    End If
    
    Unload Me
    
End Sub