Error in VBA code

A

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
GWteB

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
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.
 
Upvote 0
A

Aretradeser

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

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
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:
Upvote 0
Fluff

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
83,268
Office Version
  1. 365
Platform
  1. Windows
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
GWteB

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
Office Version
  1. 2013
Platform
  1. Windows
Fluff said:
Another option
Click to expand...
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
A

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
173
Office Version
  1. 2013
Platform
  1. Windows
GWteB said:
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.
Click to expand...
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
GWteB

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
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?
 
Upvote 0
A

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
173
Office Version
  1. 2013
Platform
  1. Windows
GWteB said:
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?
Click to expand...
Runtime error "1004" occurred.
error defined by the application or object
 
Upvote 0
Fluff

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
83,268
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
 
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
328
Aretradeser
A
T
  • Solved
modifying code by add textboxes on userform to search between two dates
Replies
4
Views
235
tubrak
T
R
  • Solved
how to add textbox as search for listbox?
Replies
3
Views
177
raz355
R
P
  • Solved
Encountering Runtime Error When Entering Date in Textbox
Replies
2
Views
91
pmgibs
P
I
  • Solved
Run time error 13 when closing worksheet
Replies
4
Views
93
ipbr21054
I

Forum statistics

Threads
1,191,395
Messages
5,986,354
Members
440,018
Latest member
GolfOne

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
Top