Advice for Could Not Find The Specific Object message

[ipbr21054]

The code is shown below.
I complete the Textboxes & select from Comboboes but when i try to send values to worksheet i see the following RTE.

When i debug the following line is in Red.
Please advise what this means.

Private Sub CommandButton1_Click()
      If OptionButton1.Value = True And OptionButton7.Value = False And OptionButton8.Value = False _
      And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
     
     MsgBox "You Must Select A Lead Type", vbCritical, "Lead Type Selection Error Message"
Else
    
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
      ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
    Next i
    
    With ThisWorkbook.Worksheets("MCLIST")
      .Range("A8").EntireRow.Insert Shift:=xlDown
      .Range("A8:K8").Borders.Weight = xlThin
      .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      
      If ComboBox3.Value = "HONDA" Then
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
      .Cells(8, 9).Font.Color = vbRed
      Else
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
      .Cells(8, 9).Font.Color = vbBlack
      End If
      
      If Me.ComboBox5.Value = "HISS CABLE ONLY" Or Me.ComboBox5.Value = "CLONE CHIP ONLY" Then
      .Cells(8, 8).Value = "N/A"
               
      End If
          
      If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
      If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
      If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
      If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
      If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
      If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
      If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
      If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
      
      If ComboBox3.Value = "HONDA" Then
      ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                 "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
      For i = 0 To UBound(ns)
        If Mid(Range("B8").Value, 10, 1) = ns(i) Then
          Range("I8").Value = "" & 2000 + i
          Exit For
        End If
      Next
      End If
      
      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
      .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
      Application.Goto Selection, True
    End With
    ActiveWorkbook.Save
    MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload McListForm
  
  End If
  
    Select Case ComboBox3.Value
        Case "APRILIA", "KAWASAKI", "PIAGGIO", "SUZUKI", "YAMAHA"
            MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
            Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Offset(, 8).Select
    End Select
    
End Sub

 

[ipbr21054]

I see the code shows For i = 1 To 8

Looking on my form i have the following of which ALL are filled / selected & a value is then shown.

TextBox
1

ComboBox
3,4,5,6,7,8 & 9

So if im correct that makes up the 8 in the code i = 1 To 8

 

[NoSparks]

1 to 8 includes 2
so when i = 2 you would be dealing with the False portion but apparently you have no TextBox2

try

    Dim i As Long, x As Long, y As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    i = 1
    For y = 1 To 9
      If y = 2 Then y = 3
      ControlsArr(i) = Controls(IIf(y > 2, "ComboBox", "TextBox") & y).Value
      'Debug.Print y, i, ControlsArr(i)
      i = i + 1
    Next y

 

[ipbr21054]

That sent from userform to sheet fine BUT now the values are in the incorrect cells.

I will look to change them & message back

 

[ipbr21054]

The values being sent from form to sheet are all over the place.
Would it be better to rename something to have it as number 2 so its then 1 To 8 if you know what i mean.

Dont want to mess up what i have by trying it myself so form to sheet is back to the way it was.

 

[ipbr21054]

If i were to add a Textbox or ComboBox followed by 2 & using my original code in my first post would that fix it ?

 

[NoSparks]

If "back to the way it was" means doing it the easy way and just writing the control values directly to the sheet then why would you want to change it ?

 

[ipbr21054]

I posted as yours came through,please see post #6

 

[NoSparks]

Try it and use F8 to step through the loop and find out

 

[ipbr21054]

I added TextBox 2 the values are sent to the sheet with no errors.
I didnt put a value in the new Textbox.

But i did notice that the value from ComboBox9 wasnt entered in the worksheet cell but i did enter a value on the userform

I get no errors at all