Error in VBA code

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
171
Office Version
  1. 2013
Platform
  1. Windows
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?
Rich (BB code):
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

Board Regular
Joined
Jan 16, 2013
Messages
171
Office Version
  1. 2013
Platform
  1. Windows
Hi,
untested but see if this update to your original code does what you want

VBA Code:
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,307
Office Version
  1. 2019
Platform
  1. Windows
No worries - glad we were able to resolve for you & we appreciate the acknowledgement.

Dave
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,409
Messages
5,831,448
Members
430,069
Latest member
bubbleboom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top