Error in VBA code

[Aretradeser]

I am trying to adapt the VBA code of a UserForm, with 3 ComboBoxes and 4 TextBoxes, to record data. At the moment, this code is giving me an error in the redmarked line. How can I solve it?

Private Sub cmdbRegistrar_Click()
    Dim Salir       As Boolean
    Dim fe1         As Date
    Dim hora        As Date
    Dim NotComplete As Boolean
    Dim i           As Integer
    For i = 1 To 7
            With Me.Controls(Choose("ComboBox", "TextBox") & i)
                If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbRed)
                If .BackColor = vbRed Then NotComplete = True
            End With
    Next i
    If NotComplete Then
        MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation, "Entry Required"
        Exit Sub
    End If
    If IsDate(TextBox2) Then fe1 = CDate(TextBox2)
    fe1 = CDate(TextBox2)
    TextBox2 = Format(fe1, "mm/dd/yyyy")
    If Not IsDate(TextBox2) Then
        MsgBox "PLEASE ENTER A VALID DATE", vbExclamation
        TextBox3.SetFocus
        Exit Sub
    End If
    hora = TimeValue((Time))
    TextBox3 = Format(hora, "hh:mm")
    Sheets("DATOS").Unprotect ("123")
    With Worksheets("DATOS")
        t = Cells(Rows.Count, 1).End(xlUp).Row
        Cells(t, 1) = TextBox2.Value
        Cells(t, 2) = TextBox3.Value
        Cells(t, 5) = ComboBox2
        Cells(t, 6) = ComboBox3
        Cells(t, 7) = TextBox4.Value
        Cells(t, 10) = TextBoxt.Value
        Cells(t, 11) = ComboBox1
    End With
    Cells(t, 11).Select
    Selection.AutoFill Destination:=Range("K" & t & ":K" & t + 1), Type:=xlFillDefault
    Sheets("DATOS").Protect ("123")
    For n = 1 To 7
        On Error Resume Next
        Controls("textbox" & n) = ""
        Controls("combobox" & n) = ""
    Next
    Sheets("DATOS").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

 

[Aretradeser]

Hi,
untested but see if this update to your original code does what you want

Option Base 1
Private Sub cmdbRegistrar_Click()
    Dim NotComplete As Boolean
    Dim i           As Long, t As Long, c As Long
    Dim arr()       As Variant
    Dim ControlsArr As Variant
  
    ControlsArr = Array("TextBox2", "TextBox3", "ComboBox2", "ComboBox3", _
             "TextBox4", "TextBoxt", "ComboBox1")
  
    ReDim arr(1 To UBound(ControlsArr))
  
    For i = 1 To UBound(arr)
  
        With Me.Controls(ControlsArr(i))
            If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbRed)
            If .BackColor = vbRed Then NotComplete = True
          
            If i < 3 And IsDate(.Value) Then
                arr(i) = IIf(i = 1, DateValue(.Value), TimeValue(.Value))
            ElseIf IsNumeric(.Value) Then
                arr(i) = Val(.Value)
            Else
                arr(i) = .Value
            End If
          
        End With
      
    Next i
  
    If NotComplete Then
        MsgBox "MISSING DATA To BE FILLED IN !!!", vbExclamation, "Entry Required"
        Exit Sub
    End If
  
    With Worksheets("DATOS")
        .Unprotect ("123")
      
        t = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
      
        For i = 1 To UBound(arr)
            c = Choose(i, 1, 2, 5, 6, 7, 10, 11)
            .Cells(t, c).Value = arr(i)
            Me.Controls(ControlsArr(i)).Value = ""
        Next
      
        .Cells(t, 1).NumberFormat = "mm/dd/yyyy"
        .Cells(t, 2).NumberFormat = "hh:mm"
      
        .Cells(t, 11).AutoFill Destination:=Range("K" & t & ":K" & t + 1), Type:=xlFillDefault
      
        .UsedRange.EntireColumn.AutoFit
      
        .Protect ("123")
    End With
  
    MsgBox "Record Added", 64, "Record Added"
  
End Sub

Dave

Perfect, Dave. Very grateful for your extraordinary work and I apologise if, at times, I have not been able to convey to you what I needed to. All due to my lack of knowledge about the VBA world. I am very fortunate to have this group of people who do this selfless work. Thank you very much.
I extend my thanks to Fluzz and GWteB.

 

[dmt32]

No worries - glad we were able to resolve for you & we appreciate the acknowledgement.

Dave