UserForm for data entry: force certain controls to be filled in.

A

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
176
Office Version
  1. 2013
Platform
  1. Windows
A Userform, used as a form for data entry, consists of several TextBoxes and ComboBoxes for data entry.
By means of these VBA code lines some of them are forced to be filled in:
CODE:
VBA Code: 
    If ComboBox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
        ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
        TextBox5 = Empty Or TextBox6 = Empty Or TextBox7 = Empty Then
        MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
    Else

The problem occurs when in the ComboBox1 an element other than "SPAIN" or "PORTUGAL" is selected, which disables the ComboBox6 and the TextBox7; as they cannot be filled in, the data cannot be recorded.
How to solve this obligation to fill in when these controls are disabled (ComboBox6 and TextBox7).
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Hi

Maybe check to see if combox1 is spain or portugal, if it is then execute your code, otherwise execute the else code that excludes them to be checked.


VBA Code: 
If combobox1 = "SPAIN" Or combobox1 = "PORTUGAL" Then
    If combobox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
    ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
    TextBox5 = Empty Or TextBox6 = Empty Or TextBox7 = Empty Then
    MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
Else
    If combobox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
    ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
    TextBox5 = Empty Then
    MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
End If
 
Upvote 0
Hi,

not tested but try

VBA Code: 
    Dim NotComplete     As Boolean
    Dim i               As Integer, a As Integer
    
    For i = 1 To 7

        For a = 1 To 2
          If i > 6 Then a = 2
            With Me.Controls(Choose(a, "ComboBox", "TextBox") & i)
                If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbYellow)
                If .BackColor = vbYellow Then NotComplete = True
            End With
    
        Next a
        
    Next i
    
    If NotComplete Then
        MsgBox "MISSING DATA To BE FILLED IN !!!", vbExclamation, "Entry Required"
    End If

Dave
 
Last edited:
Upvote 0
another thing you could do it to check if ComboBox6 or ComboBox7 is enabled.

example below.

If TextBox6.Enabled = true or TextBox6 = Empty then
 
Upvote 0
SQUIDD said:
Hi

Maybe check to see if combox1 is spain or portugal, if it is then execute your code, otherwise execute the else code that excludes them to be checked.


VBA Code: 
If combobox1 = "SPAIN" Or combobox1 = "PORTUGAL" Then
    If combobox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
    ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
    TextBox5 = Empty Or TextBox6 = Empty Or TextBox7 = Empty Then
    MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
Else
    If combobox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
    ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
    TextBox5 = Empty Then
    MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
End If
Click to expand...
Error in this line of code that I mark in red:
Rich (BB code): 
Private Sub cmdbRegistrar_Click()
    Dim Salir As Boolean
    Dim fe1 As Date
    Dim fe2 As Date
   
    Dim NotComplete     As Boolean
    Dim i               As Integer, a As Integer
   
    For i = 1 To 7
        For a = 1 To 2
          If i > 6 Then a = 2
            With Me.Controls(Choose(a, "ComboBox", "TextBox") & i)
                If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbYellow)
                If .BackColor = vbYellow Then NotComplete = True
            End With   
        Next a       
    Next i   
    If NotComplete Then
        MsgBox "MISSING DATA To BE FILLED IN !!!", vbExclamation, "Entry Required"
    End If
        fe1 = CDate(TextBox1)
        TextBox1 = Format(fe1, "mm/dd/yyyy")
        If Not IsDate(TextBox2) Then
            MsgBox "INGRESE UNA FECHA VÁLIDA", vbExclamation
            TextBox2.SetFocus
            Exit Sub
        End If
        fe2 = CDate(TextBox2)
        TextBox2 = Format(fe2, "mm/dd/yyyy")
        Sheets("BDATOS").Select
        Sheets("BDATOS").Unprotect ("123")
        With Worksheets("BDATOS")
            t = Cells(Rows.Count, 2).End(xlUp).Row
            Cells(t + 1, 2) = TextBox6 & " " & TextBox7 & ", " & TextBox5
            Cells(t + 1, 3) = ComboBox5
            Cells(t + 1, 4) = ComboBox5 '
            Cells(t + 1, 5) = ComboBox1 '
            Cells(t + 1, 6) = ComboBox2
            Cells(t + 1, 7) = TextBox1.Value
            Cells(t + 1, 8) = TextBox2.Value
            Cells(t + 1, 9) = ComboBox3
            Cells(t + 1, 10) = ComboBox4
            Cells(t + 1, 11) = 0 + TextBox3
            Cells(t + 1, 12) = TextBox4.Value
            Cells(t + 1, 13) = CmbSexo
            Cells(t + 1, 14) = Right(TextBox1.Value, 4)
        End With
        Cells(t, 13).Select
        Selection.AutoFill Destination:=Range("M" & t & ":M" & t + 1), Type:=xlFillDefault
        Sheets("BDATOS").Protect ("acuario3511")
        For n = 1 To 12
            On Error Resume Next
            Controls("textbox" & n) = ""
            Controls("combobox" & n) = ""
        Next
        Sheets("DATOS").Select
        Cells.Select
        Cells.EntireColumn.AutoFit
   
End Sub
dmt32 said:
Hi,

not tested but try

VBA Code: 
    Dim NotComplete     As Boolean
    Dim i               As Integer, a As Integer
  
    For i = 1 To 7

        For a = 1 To 2
          If i > 6 Then a = 2
            With Me.Controls(Choose(a, "ComboBox", "TextBox") & i)
                If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbYellow)
                If .BackColor = vbYellow Then NotComplete = True
            End With
  
        Next a
      
    Next i
  
    If NotComplete Then
        MsgBox "MISSING DATA To BE FILLED IN !!!", vbExclamation, "Entry Required"
    End If

Dave
Click to expand...

dmt32 said:
Hi,

not tested but try

VBA Code: 
    Dim NotComplete     As Boolean
    Dim i               As Integer, a As Integer
   
    For i = 1 To 7

        For a = 1 To 2
          If i > 6 Then a = 2
            With Me.Controls(Choose(a, "ComboBox", "TextBox") & i)
                If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbYellow)
                If .BackColor = vbYellow Then NotComplete = True
            End With
   
        Next a
       
    Next i
   
    If NotComplete Then
        MsgBox "MISSING DATA To BE FILLED IN !!!", vbExclamation, "Entry Required"
    End If

Dave
Click to expand...
Error in this line of code that I mark in red:
Rich (BB code): 
Private Sub cmdbRegistrar_Click()
    Dim Salir As Boolean
    Dim fe1 As Date
    Dim fe2 As Date
    
    Dim NotComplete     As Boolean
    Dim i               As Integer, a As Integer
    
    For i = 1 To 7

        For a = 1 To 2
          If i > 6 Then a = 2
            With Me.Controls(Choose(a, "ComboBox", "TextBox") & i)
                If .Enabled Then .BackColor = IIf(Len(.Text) > 0, vbWindowBackground, vbYellow)
                If .BackColor = vbYellow Then NotComplete = True
            End With    
        Next a        
    Next i    
    If NotComplete Then
        MsgBox "MISSING DATA To BE FILLED IN !!!", vbExclamation, "Entry Required"
    End If
        fe1 = CDate(TextBox1)
        TextBox1 = Format(fe1, "mm/dd/yyyy")
        If Not IsDate(TextBox2) Then
            MsgBox "INGRESE UNA FECHA VÁLIDA", vbExclamation
            TextBox2.SetFocus
            Exit Sub
        End If
        fe2 = CDate(TextBox2)
        TextBox2 = Format(fe2, "mm/dd/yyyy")
        Sheets("BDATOS").Select
        Sheets("BDATOS").Unprotect ("123")
        With Worksheets("BDATOS")
            t = Cells(Rows.Count, 2).End(xlUp).Row
            Cells(t + 1, 2) = TextBox6 & " " & TextBox7 & ", " & TextBox5
            Cells(t + 1, 3) = ComboBox5
            Cells(t + 1, 4) = ComboBox5 '
            Cells(t + 1, 5) = ComboBox1 '
            Cells(t + 1, 6) = ComboBox2
            Cells(t + 1, 7) = TextBox1.Value
            Cells(t + 1, 8) = TextBox2.Value
            Cells(t + 1, 9) = ComboBox3
            Cells(t + 1, 10) = ComboBox4
            Cells(t + 1, 11) = 0 + TextBox3
            Cells(t + 1, 12) = TextBox4.Value
            Cells(t + 1, 13) = CmbSexo
            Cells(t + 1, 14) = Right(TextBox1.Value, 4)
        End With
        Cells(t, 13).Select
        Selection.AutoFill Destination:=Range("M" & t & ":M" & t + 1), Type:=xlFillDefault
        Sheets("BDATOS").Protect ("acuario3511")
        For n = 1 To 12
            On Error Resume Next
            Controls("textbox" & n) = ""
            Controls("combobox" & n) = ""
        Next
        Sheets("DATOS").Select
        Cells.Select
        Cells.EntireColumn.AutoFit    
End Sub
 
Upvote 0
Hi

So i cannot see that you have tested my code?

Dave
 
Upvote 0
SQUIDD said:
Hi

So i cannot see that you have tested my code?

Dave
Click to expand...
Sorry, as you may have seen, I got a bit confused when answering; but yes, I tried it and it doesn't work, unless I didn't put the line of code in the right place.
 
Upvote 0
I see

Can you post your full code as it was before any changes were made ?
 
Upvote 0
SQUIDD said:
I see

Can you post your full code as it was before any changes were made ?
Click to expand...
There you have it:
VBA Code: 
'
'
Private Sub cmdbRegistrar_Click()
    Dim Salir As Boolean
    Dim fe1 As Date
    Dim fe2 As Date
    
    If ComboBox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
        ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
        TextBox5 = Empty Or TextBox6 = Empty Or TextBox7 = Empty Then
        MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
    Else
        fe1 = CDate(TextBox1)
        TextBox1 = Format(fe1, "mm/dd/yyyy")
        If Not IsDate(TextBox2) Then
            MsgBox "INGRESE UNA FECHA VÁLIDA", vbExclamation
            TextBox2.SetFocus
            Exit Sub
        End If
        fe2 = CDate(TextBox2)
        TextBox2 = Format(fe2, "mm/dd/yyyy")
        Sheets("BDATOS").Select
        Sheets("BDATOS").Unprotect ("123")
        With Worksheets("BDATOS")
            Cells(t + 1, 2) = TextBox6 & " " & TextBox7 & ", " & TextBox5
            Cells(t + 1, 3) = ComboBox5
            Cells(t + 1, 4) = ComboBox5 '
            Cells(t + 1, 5) = ComboBox1 '
            Cells(t + 1, 6) = ComboBox2
            Cells(t + 1, 7) = TextBox1.Value
            Cells(t + 1, 8) = TextBox2.Value
            Cells(t + 1, 9) = ComboBox3
            Cells(t + 1, 10) = ComboBox4
            Cells(t + 1, 11) = 0 + TextBox3
            Cells(t + 1, 12) = TextBox4.Value
            Cells(t + 1, 13) = CmbSexo
            Cells(t + 1, 14) = Right(TextBox1.Value, 4)
        End With
        Cells(t, 13).Select
        Selection.AutoFill Destination:=Range("M" & t & ":M" & t + 1), Type:=xlFillDefault
        Sheets("BDATOS").Protect ("acuario3511")
        For n = 1 To 12
            On Error Resume Next
            Controls("textbox" & n) = ""
            Controls("combobox" & n) = ""
        Next
        Sheets("DATOS").Select
        Cells.Select
        Cells.EntireColumn.AutoFit
    End If
End Sub
 
Upvote 0
ok

so i would think this would work for you?

VBA Code: 
Private Sub cmdbRegistrar_Click()
    Dim Salir As Boolean
    Dim fe1 As Date
    Dim fe2 As Date

If combobox1 = "SPAIN" Or combobox1 = "PORTUGAL" Then
    If combobox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
    ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
    TextBox5 = Empty Or TextBox6 = Empty Or TextBox7 = Empty Then
    MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
Else
    If combobox1 = Empty Or ComboBox2 = Empty Or ComboBox3 = Empty Or ComboBox4 = Empty Or ComboBox5 = Empty Or _
    ComboBox6 = Empty Or TextBox1 = Empty Or TextBox2 = Empty Or TextBox3 = Empty Or TextBox4 = Empty Or _
    TextBox5 = Empty Then
    MsgBox "MISSING DATA TO BE FILLED IN !!!", vbExclamation: Exit Sub
End If

        fe1 = CDate(TextBox1)
        TextBox1 = Format(fe1, "mm/dd/yyyy")
        If Not IsDate(TextBox2) Then
            MsgBox "INGRESE UNA FECHA VÁLIDA", vbExclamation
            TextBox2.SetFocus
            Exit Sub
        End If
        fe2 = CDate(TextBox2)
        TextBox2 = Format(fe2, "mm/dd/yyyy")
        Sheets("BDATOS").Select
        Sheets("BDATOS").Unprotect ("123")
        With Worksheets("BDATOS")
            Cells(t + 1, 2) = TextBox6 & " " & TextBox7 & ", " & TextBox5
            Cells(t + 1, 3) = ComboBox5
            Cells(t + 1, 4) = ComboBox5 '
            Cells(t + 1, 5) = combobox1 '
            Cells(t + 1, 6) = ComboBox2
            Cells(t + 1, 7) = TextBox1.Value
            Cells(t + 1, 8) = TextBox2.Value
            Cells(t + 1, 9) = ComboBox3
            Cells(t + 1, 10) = ComboBox4
            Cells(t + 1, 11) = 0 + TextBox3
            Cells(t + 1, 12) = TextBox4.Value
            Cells(t + 1, 13) = CmbSexo
            Cells(t + 1, 14) = Right(TextBox1.Value, 4)
        End With
        Cells(t, 13).Select
        Selection.AutoFill Destination:=Range("M" & t & ":M" & t + 1), Type:=xlFillDefault
        Sheets("BDATOS").Protect ("acuario3511")
        For n = 1 To 12
            On Error Resume Next
            Controls("textbox" & n) = ""
            Controls("combobox" & n) = ""
        Next
        Sheets("DATOS").Select
        Cells.Select
        Cells.EntireColumn.AutoFit
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
188
Patriot2879
P
E
  • Solved
Userform as search tool
Replies
5
Views
261
Akuini
Akuini
R
  • Question
Msg box for
Replies
1
Views
95
Micron
M
P
  • Solved
Code doesn't go on a new line (emptyRow)
Replies
8
Views
230
Fluff
Fluff
X
  • Question
userform auto fill all
Replies
1
Views
261
Micron
M

Forum statistics

Threads
1,215,028
Messages
6,122,753
Members
449,094
Latest member
dsharae57

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