Error in VBA code

Aretradeser

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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
151
Office Version
  1. 2013
Platform
  1. Windows
I want to make it mandatory to fill in all ComboBoxes and TextBoxes, except for TextBox1.
Thank you
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
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
 

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
151
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
151
Office Version
  1. 2013
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
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
 
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,152,578
Messages
5,770,956
Members
425,653
Latest member
UNSING

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