Control mandatory fields from proceeding

Question

so i have a user form that i am working on for a while and i have made some fields mandatory using the code below

If Me.DateBox.Value = "" Then

DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"

End If

now the problem is although this does mark the empty field red and display a message, it just allow the user to processed exporting data to excel after executing the above code. but what i want it to do is to not let the user transfer information without filling the empty text box.

here is the full code..

Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long

Set wf = Application.WorksheetFunction

Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
        addlist = Me.WortSelector.List(x)
        Set addlist = addlist.Offset(1, 0)
        addlist2 = Me.WortSelector.List(x)
        Set addlist2 = addlist2.Offset(1, 0)
        End If
        Next x


lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
        Cells(lNextRow, 2) = DateBox.Text
        Cells(lNextRow, 3) = PrBox.Text
        Cells(lNextRow, 4) = BrewBox.Text
        Cells(lNextRow + 1, 9) = RmBox1.Text
        Cells(lNextRow, 10) = OgBox.Text
        Cells(lNextRow + 2, 9) = RmBox2.Text
        Cells(lNextRow + 3, 9) = RmBox3.Text
        Cells(lNextRow + 4, 9) = RmBox4.Text
        Cells(lNextRow + 5, 9) = RmBox5.Text
        Cells(lNextRow + 6, 9) = RmBox6.Text
        Cells(lNextRow + 7, 9) = RmBox7.Text
        Cells(lNextRow + 8, 9) = RmBox8.Text
        Cells(lNextRow + 9, 9) = RmBox9.Text
        Cells(lNextRow + 10, 9) = RmBox10.Text
        Cells(lNextRow + 11, 9) = RmBox11.Text
        Cells(lNextRow + 12, 9) = RmBox12.Text
        Cells(lNextRow + 1, 8) = rm1
        Cells(lNextRow + 2, 8) = rm2
        Cells(lNextRow + 3, 8) = rm3
        Cells(lNextRow + 4, 8) = rm4
        Cells(lNextRow + 5, 8) = rm5
        Cells(lNextRow + 6, 8) = rm6
        Cells(lNextRow + 7, 8) = rm7
        Cells(lNextRow + 8, 8) = rm8
        Cells(lNextRow + 9, 8) = rm9
        Cells(lNextRow + 10, 8) = rm10
        Cells(lNextRow + 11, 8) = rm11
        Cells(lNextRow + 12, 8) = rm12

        Cells(lNextRow, 9) = VolumeBox.Text

Do

    Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
    For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
        addlist = Me.WortSelector.List(x)
        End If
        Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
     Cells(lNextRow, 2) = DateBox.Text
        Cells(lNextRow, 3) = PrBox.Text
        Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""

If Me.DateBox.Value = "" Then

DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"

End If

If Me.PrBox.Value = "" Then

PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
End If

If Me.BrewBox.Value = "" Then

BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"

End If


End Sub

Thanks!

Apply better logic to your code: After the CommandButton is clicked first check if PrBox is not empty/correctly filled. If it isn't, set focus on the red PrBox and exit the sub. Repeat with BrewBox. Only after the form is found to be filled correctly start transferring data elsewhere. — Variatus, Jan 02 '19 at 08:42

Santosh · Accepted Answer · 2019-01-02T08:45:07.617

0

Add Exit Sub as shown below. Its stops code execution further.

If Me.DateBox.Value = "" Then

   DateBox.BackColor = vbRed
   MsgBox "Date Field Can Not be Empty"
   Exit sub
End If

Private Sub CommandButton1_Click()
    Dim addlist As Range
    Dim x As Integer
    Dim wf As WorksheetFunction
    Dim y As Integer
    Dim addlist2 As Range
    Dim lNextRow As Long
    Dim ans As Long

    'Validation start
    If Me.DateBox.Value = "" Then

        DateBox.BackColor = vbRed
        MsgBox "Date Field Can Not be Empty"
        Exit Sub
    End If

    If Me.PrBox.Value = "" Then

        PrBox.BackColor = vbRed
        MsgBox "PR No. Field Can Not be Empty"
        Exit Sub

    End If

    If Me.BrewBox.Value = "" Then

        BrewBox.BackColor = vbRed
        MsgBox "Brew Number Field Can Not be Empty"
        Exit Sub

    End If
    'Validation end

    Set wf = Application.WorksheetFunction

    Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
    Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
    For x = 1 To WortSelector.ListCount - 1
        If Me.WortSelector.Selected(x) Then
            addlist = Me.WortSelector.List(x)
            Set addlist = addlist.Offset(1, 0)
            addlist2 = Me.WortSelector.List(x)
            Set addlist2 = addlist2.Offset(1, 0)
        End If
    Next x


    lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
    Cells(lNextRow, 2) = DateBox.Text
    Cells(lNextRow, 3) = PrBox.Text
    Cells(lNextRow, 4) = BrewBox.Text
    Cells(lNextRow + 1, 9) = RmBox1.Text
    Cells(lNextRow, 10) = OgBox.Text
    Cells(lNextRow + 2, 9) = RmBox2.Text
    Cells(lNextRow + 3, 9) = RmBox3.Text
    Cells(lNextRow + 4, 9) = RmBox4.Text
    Cells(lNextRow + 5, 9) = RmBox5.Text
    Cells(lNextRow + 6, 9) = RmBox6.Text
    Cells(lNextRow + 7, 9) = RmBox7.Text
    Cells(lNextRow + 8, 9) = RmBox8.Text
    Cells(lNextRow + 9, 9) = RmBox9.Text
    Cells(lNextRow + 10, 9) = RmBox10.Text
    Cells(lNextRow + 11, 9) = RmBox11.Text
    Cells(lNextRow + 12, 9) = RmBox12.Text
    Cells(lNextRow + 1, 8) = rm1
    Cells(lNextRow + 2, 8) = rm2
    Cells(lNextRow + 3, 8) = rm3
    Cells(lNextRow + 4, 8) = rm4
    Cells(lNextRow + 5, 8) = rm5
    Cells(lNextRow + 6, 8) = rm6
    Cells(lNextRow + 7, 8) = rm7
    Cells(lNextRow + 8, 8) = rm8
    Cells(lNextRow + 9, 8) = rm9
    Cells(lNextRow + 10, 8) = rm10
    Cells(lNextRow + 11, 8) = rm11
    Cells(lNextRow + 12, 8) = rm12

    Cells(lNextRow, 9) = VolumeBox.Text

    Do

        Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
        For x = 1 To WortSelector.ListCount - 1
            If Me.WortSelector.Selected(x) Then
                addlist = Me.WortSelector.List(x)
            End If
        Next x
        lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
        Cells(lNextRow, 2) = DateBox.Text
        Cells(lNextRow, 3) = PrBox.Text
        Cells(lNextRow, 4) = BrewBox.Text
    Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""

End Sub

edited Jan 02 '19 at 08:45

answered Jan 02 '19 at 08:34

Santosh

12,175
4
41
72

Hi Santosh, i did but still nothing.. it does the same thing – ermias dillensaw Jan 02 '19 at 08:38
Move your validation statements after the code starts executing and not at the end. – Santosh Jan 02 '19 at 08:42
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that – ermias dillensaw Jan 02 '19 at 09:36
@ermiasdillensaw You can add this line at then end of validation `If Me.PrBox.Value = Me.BrewBox.Value Then msgbox "Invalid Value" exit sub end if` – Santosh Jan 02 '19 at 09:39
i get what you're saying but the code will only check whether the user types the same information in the PrBox and BrewBox.. what i want is to prevent user from entering the same data in BrewBox and the same data in PrBox as well. For example if 12 is used once in BrewBox it should never be used again because it is a unique id kinda thing.. – ermias dillensaw Jan 02 '19 at 09:43
In that case you can define a dictionary and store the value. This will allow you to easily check if the value already exist in dictionary. – Santosh Jan 02 '19 at 09:45
still don't figure out how to restrict duplicate in column D. – ermias dillensaw Jan 02 '19 at 12:01
Or use a simple collection object. Collection is a list which store key & value pairs and has unique key. See this link https://stackoverflow.com/a/18800150/2227085 – Santosh Jan 02 '19 at 12:17
i actually dont know how it is not working because it really should be simple.. for example the following code just returns "Argument not optional" and i dont know what's wrong.. If Application.WorksheetFunction.CountIf(Range("D,D").Me.BrewBox) > 0 Then MsgBox "Duplicate Exist in the Database" Me.BrewBox.Value = "" Exit Sub End If – ermias dillensaw Jan 02 '19 at 12:31
@ermiasdillensaw don't worry, I will update the code in sometime – Santosh Jan 02 '19 at 12:36
oh okay.. Thank you so much! – ermias dillensaw Jan 02 '19 at 12:38
It should be `If WorksheetFunction.CountIf(Range("D:D"), Me.BrewBox.Value) > 0 Then MsgBox "Duplicate Exist in the Database" Me.BrewBox.Value = "" Exit Sub End If` – Santosh Jan 03 '19 at 04:21
wow it works like charm now!! thank you so very much Santosh that's genius! – ermias dillensaw Jan 03 '19 at 05:57
i am currently working on multiple projects including power bi so like i am always asking.. do you by any chance know power bi? – ermias dillensaw Jan 03 '19 at 06:06

Control mandatory fields from proceeding

1 Answers1