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

 

[dmt32]

Dave, I have corrected the line of code below, because you had put TextBoxt, instead of TextBox1

ControlsArr = Array("TextBox2", "TextBox3", "ComboBox2", "ComboBox3", _
             "TextBox4", "TextBoxt", "ComboBox1")

; but still, the code gives me the following error:
Runtime error 6 occurred:
Overflow

Control name TextBoxt was taken from code shown your original post

Helpful if could place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it.

Dave

 

[Aretradeser]

dmt32, I have tested your code and it has several errors:
The values of the TextBox2 does not register them; with which, the registration of the rest of the data do not coincide in the columns in which they should go.
Even if the TextBox2 is not filled in, it records the data, but the condition is that all TextBoxes and ComboBoxes must be filled in for the record to be allowed.
The TextBox2 is not formatted properly. The last record in "A25", in the Userform I put "30112021" and it records it as "01/00/1900".
I leave this link to the example Excel file
Example.xlsm

Dave, there you have it. I put it a few posts back.

 

[dmt32]

Dave, there you have it. I put it a few posts back.

and a few posts back #17 I asked if you had added Option Base 1 to the TOP of your forms code page.
You need to add this statement as I detailed in the post.

Dave

 

[Aretradeser]

and a few posts back #17 I asked if you had added Option Base 1 to the TOP of your forms code page.
You need to add this statement as I detailed in the post.

and a few posts back #17 I asked if you had added Option Base 1 to the TOP of your forms code page.
You need to add this statement as I detailed in the post.

Dave

The error that I have reported in the previous post, occurs already with the addition OPTION BASE 1 at the top of the page of the code of the forms. Of course, when you asked me for an example of the Excel workbook, I thought you were doing it to make some checks to see what might be going wrong in the proposed code; but, evidently, the example file is older and therefore lacks your kind solution and: OPTION BASE 1. Sorry. If you require any further explanation, I will be happy to provide it. In any case, I would like to express my sincere thanks for your help.

 

[dmt32]

I was expecting to see the version you are using - With Option Base 1 placed at top of your form, code post data to correct columns with no issues for me.

If you are still have problems, post the version you are using in the dropbox

Dave

 

[Aretradeser]

I was expecting to see the version you are using - With Option Base 1 placed at top of your form, code post data to correct columns with no issues for me.

If you are still have problems, post the version you are using in the dropbox

Dave

If you are referring to the office version, it is 2013.

 

[Aretradeser]

I will prepare a sample Excel file, updated with your input, so that you can check the error. It will take a while.

 

[dmt32]

Upload the latest copy of the workbook you are having issues with.

Dave

 

[Aretradeser]

Upload the latest copy of the workbook you are having issues with.

Dave

When preparing the example, I have solved the error I was getting, when opening the UserForm1 (cmdbRegistrar).
Now, I have two small problems, when using the UserForm2 (cmdbRegistrar1):
1.- That when opening this UserForm2, before being seen, it jumps the warning "ENTER ONLY THE DATE FOLLOWED BY THE DATE, WITHOUT HYPHENS OR SLASHES".
2.- I don't want to manually enter data in the TextBox4 (TOTAL KMS.), but I want it to take the value of the difference KMS. travelled (KMS. END - KMS. START).
I upload in Dropbox the Excel book

Example1.xlsm

Shared with Dropbox

www.dropbox.com

 

[dmt32]

Hi,
I take it from your post that solution provided works & has solved your original issue?
The additional issues you mention with the second userform are not really related to the first post & suggest probably better to start a new thread.

Dave