Error in VBA code

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
173
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
The Choose function needs an index number as the first argument, not a text string.
You need to be more specific about what you are trying to achieve in order to increase the chance that you will get an answer that fits.
 
Upvote 0
I want to make it mandatory to fill in all ComboBoxes and TextBoxes, except for TextBox1.
Thank you
 
Upvote 0
I would go for replacing this part
Rich (BB code):
    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

with this part
VBA Code:
    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


using a separate function:
VBA Code:
Private Function IsThisNotFilledIn(ByVal argCtl As MSForms.Control) As Boolean
    With argCtl
            If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbRed)
            If .BackColor = vbRed Then IsThisNotFilledIn = True
    End With
End Function

EDIT: if you were able to switch the concerns of Textbox1 and Textbox4 then you would need just one loop.
 
Last edited:
Upvote 0
Another option
VBA Code:
    For i = 1 To 3
            With Me.Controls("ComboBox" & i)
                If .Enabled Then .BackColor = IIf(Len(.text) > 0, vbWindowBackground, vbRed)
                If .BackColor = vbRed Then NotComplete = True
            End With
            With Me.Controls("TextBox" & i + 1)
                If .Enabled Then .BackColor = IIf(Len(.text) > 0, vbWindowBackground, vbRed)
                If .BackColor = vbRed Then NotComplete = True
            End With
    Next i
 
Upvote 0
Another option
Why didn't I see that ... ?


Without switching text boxes my submission then would be:
VBA Code:
    For i = 1 To 3
        NotComplete = IsThisNotFilledIn(Me.Controls("ComboBox" & i))
        NotComplete = IsThisNotFilledIn(Me.Controls("TextBox" & i + 1))
    Next i
 
Upvote 0
I would go for replacing this part
Rich (BB code):
    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

with this part
VBA Code:
    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


using a separate function:
VBA Code:
Private Function IsThisNotFilledIn(ByVal argCtl As MSForms.Control) As Boolean
    With argCtl
            If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbRed)
            If .BackColor = vbRed Then IsThisNotFilledIn = True
    End With
End Function

EDIT: if you were able to switch the concerns of Textbox1 and Textbox4 then you would need just one loop.
After making the proposed changes, it gives error in the following line of code, which I mark in red, It is a TextBox in which data in hours and minutes format are included.
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 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")
    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) = TextBox1.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 ("acuario3511")
    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
 
Upvote 0
Run-time errors always appear in sequence of program flow.
This run-time error has nothing to do with our suggested changes.
Perhaps that you're able telling us what the error message exactly says?
 
Upvote 0
Run-time errors always appear in sequence of program flow.
This run-time error has nothing to do with our suggested changes.
Perhaps that you're able telling us what the error message exactly says?
Runtime error "1004" occurred.
error defined by the application or object
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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