Error in VBA code

A

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
176
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
 
Sort by date Sort by votes
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
 
Upvote 0
Solution

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Fluff said:
Try it like
VBA 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 3
        NotComplete = IsThisNotFilledIn(Me.Controls("ComboBox" & i))
    Next i
    For i = 2 To 4
        NotComplete = IsThisNotFilledIn(Me.Controls("TextBox" & i))
    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 ("acuario3511")
    Worksheets("DATOS").Select
        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) = TextBox1.Value
        Cells(t, 11) = ComboBox1
    Cells(t, 11).Select
    Selection.AutoFill Destination:=Range("K" & t & ":K" & t + 1), Type:=xlFillDefault
    Sheets("DATOS").Protect ("acuario3511")
    For n = 1 To 7
        On Error Resume Next
        Controls("textbox" & n) = ""
        Controls("combobox" & n) = ""
    Next
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub
Click to expand...
Thanks for your reply, Fluff; but it keeps giving the same error on the same line of VBA code.
Could it be that this error is caused by the format in which the data is entered in the TextBox3? I enter them in this format: 14:30; perhaps this TextBox should be configured so that they are entered in this or another format, e.g. consecutive numbers, without punctuation: 1430.
 
Upvote 0
Have you tried the code that dmt32 supplied?
 
Upvote 0
Thank you for your answer, dmt32. When executing your code it gives this error, in this line of code:
Rich (BB code): 
 With Me.Controls(ControlsArr(i))
"Runtime error -2147024809 (80070057) occurred: the specified object is not found.
 
Upvote 0
Check the names in the array (I took from your first post) match each control name in your userform - update if required.

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

Dave
 
Upvote 0
dmt32 said:
Check the names in the array (I took from your first post) match each control name in your userform - update if required.

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

Dave
Click to expand...
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
 
Upvote 0
Hi,
when you copied the code did you include this line to your userforms code page

Rich (BB code): 
Option Base 1

This MUST go at the very TOP of your forms code page OUTSIDE any procedure

The order the controls in the ARRAY are placed determine the order they will be posted in your worksheet (which I based on your information in your main post.)

The order the Columns being posted to is 1, 2, 5, 6, 7, 10, 11

If you have included the statement above in your forms code page & code still not posting data correctly then need to adjust the order of the controls in the array.

I am out most of day but if still not resolved when I return I will have a further look

Dave
 
Upvote 0
dmt32 said:
Hi,
when you copied the code did you include this line to your userforms code page

Rich (BB code): 
Option Base 1

This MUST go at the very TOP of your forms code page OUTSIDE any procedure

The order the controls in the ARRAY are placed determine the order they will be posted in your worksheet (which I based on your information in your main post.)

The order the Columns being posted to is 1, 2, 5, 6, 7, 10, 11

If you have included the statement above in your forms code page & code still not posting data correctly then need to adjust the order of the controls in the array.

I am out most of day but if still not resolved when I return I will have a further look

Dave
Click to expand...
The order in which the controls are placed in the ARRAY is correct. The problem is that the value of the TextBox is skipped, it does not register it. In the Excel workbook that I put as an example (Example.xlsm) you can check it.
 
Upvote 0
Aretradeser said:
The order in which the controls are placed in the ARRAY is correct. The problem is that the value of the TextBox is skipped, it does not register it. In the Excel workbook that I put as an example (Example.xlsm) you can check it.
Click to expand...

Have you placed Option Base 1 at the TOP of your forms code page?

Also in the order of the Controls are listed in the Array, code is only populating these columns

1, 2, 5, 6, 7, 10, 11

which is based on the information provided in your first post.

Dave
 
Upvote 0
dmt32 said:
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
Click to expand...
Dave, I have corrected the line of code below, because you had put TextBoxt, instead of TextBox1
Rich (BB code): 
ControlsArr = Array("TextBox2", "TextBox3", "ComboBox2", "ComboBox3", _
             "TextBox4", "TextBoxt", "ComboBox1")
; but still, the code gives me the following error:
Runtime error 6 occurred:
Overflow
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Exception to the obligation to fill in all fields of a UserForm
Replies
2
Views
487
Aretradeser
A
A
  • Question
Countdown with vba
Replies
2
Views
230
asyamonique
A
A
  • Solved
make faster code after increase data for 4000 rows for each sheet on userform
2
Replies
13
Views
916
Akuini
Akuini
P
  • Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
183
Patriot2879
P
A
  • Question
VBA code to print lottery tickets base on the cell amount.
Replies
0
Views
160
asyamonique
A

Forum statistics

Threads
1,214,641
Messages
6,120,694
Members
448,979
Latest member
DET4492

Share this page

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
Back
Top