How to validate TextBox if not filled?

[epardo87]

Hello!

So I have this transferring codes and they work fine, I just want it to validate that the whole form has been filled.

I'd appreciate your suggestions!

Thanks

'Transfer information
Cells(emptyRow, 1).Value = UserTextBox.Value
Cells(emptyRow, 2).Value = TravelerTextBox.Value
Cells(emptyRow, 3).Value = AreaComboBox.Value
Cells(emptyRow, 4).Value = OperComboBox.Value
Cells(emptyRow, 5).Value = Date


If StartOptionButton.Value = True Then
    Cells(emptyRow, 6).Value = Time
Else
    Cells(emptyRow, 7).Value = Time
End If

 

[iliace]

You have to define what you mean by "the whole form has been filled". The same way you check the StartOptionButton value is how you would go about validating the rest of the form.

 

[epardo87]

Thanks, well by the whole form I just mean those 5 cells mentioned in the code.
So as you said I should write it something like this? what should I place in (???)​?

If UserTextBox.Value = [B](???)[/B] Then
Cells(emptyRow, 1).Value = UserTextBox.Value
Else
MissingUserForm.Show
End if

 

[iliace]

How about:

If Len(UserTextBox.Value) > 0 Then

and the rest just how you have it.

 

[epardo87]

Thanks, it worked! but i did it in every cells code and now if they're all empty, the pop window (MissingUserForm.Show) pops up 4 times if 4 fields are empty, how could I avoid this?

I also want that only if they are indeed empty, that the UserForm doesn't close (Unload Me)

'Transfer informationIf Len(UserTextBox.Value) > 0 Then
Cells(emptyRow, 1).Value = UserTextBox.Value
Else
MissingUserForm.Show
End If
If Len(TravelerTextBox.Value) > 0 Then
Cells(emptyRow, 2).Value = TravelerTextBox.Value
Else
MissingUserForm.Show
End If
If Len(AreaComboBox.Value) > 0 Then
Cells(emptyRow, 3).Value = AreaComboBox.Value
Else
MissingUserForm.Show
End If
If Len(OperComboBox.Value) > 0 Then
Cells(emptyRow, 4).Value = OperComboBox.Value
Else
MissingUserForm.Show
End If
Cells(emptyRow, 5).Value = Date


If StartOptionButton.Value = True Then
    Cells(emptyRow, 6).Value = Time
Else
    Cells(emptyRow, 7).Value = Time
End If


Unload Me


End Sub

 

[iliace]

Here is one possible way. Keep in mind that doing it this way will force the user to fill out all the fields (without resorting to Ctrl+Break or crashing Excel). In that context, you may want to include some way to cancel the entire operation altogether, if it doesn't already exist in your main form.

Dim bControlEmpty As Boolean


bControlEmpty = False


Do
  If Len(UserTextBox.Value) > 0 Then
    Cells(emptyRow, 1).Value = UserTextBox.Value
  Else
    bControlEmpty = True
  End If
  If Len(TravelerTextBox.Value) > 0 Then
    Cells(emptyRow, 2).Value = TravelerTextBox.Value
  Else
    bControlEmpty = True
  End If
  If Len(AreaComboBox.Value) > 0 Then
    Cells(emptyRow, 3).Value = AreaComboBox.Value
  Else
    bControlEmpty = True
  End If
  If Len(OperComboBox.Value) > 0 Then
    Cells(emptyRow, 4).Value = OperComboBox.Value
  Else
    bControlEmpty = True
  End If




''''' other code here


  If bControlEmpty Then
    MissingUserForm.Show
  End If
Loop While bControlEmpty


Unload Me

 

[epardo87]

Thanks a lot for your help iliace, you've been very patient with me and I really appreciate it, please don't lose it lol, I didn't know anything about VBA one week ago and I started learning because my IT dept gave me about two months to begging working on it, I found it a good excuse to learn by myself and couldn't be doing it without people like you in these forums!

So, I modified as you said and every field filled is transferred anyway, so if I leave only one field empty, the other three are transferred and the pop up window also appears and this window loops infinitely, without giving me a chance to complete the form, it just pops up again immediately.
I tried to eliminate the loop so that the form is closed after finding bControlEmpty but I couldn't get to avoid transferring fields filled when there still at least one empty...
This is my complete button code with the loop added, I moved the other codes (Date and Time) to an Else After the If bControlEmpty so that they're filled only if all other fields are filled (btw. I do have a cancel button in the form):
Best Regards!

Private Sub OKCommandButton_Click()

Sheet1.Unprotect Password:="2606"

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information

Dim bControlEmpty As Boolean

bControlEmpty = False


Do
    If Len(UserTextBox.Value) > 0 Then
    Cells(emptyRow, 1).Value = UserTextBox.Value
    Else
    bControlEmpty = True
    End If
    If Len(TravelerTextBox.Value) > 0 Then
    Cells(emptyRow, 2).Value = TravelerTextBox.Value
    Else
    bControlEmpty = True
    End If
    If Len(AreaComboBox.Value) > 0 Then
    Cells(emptyRow, 3).Value = AreaComboBox.Value
    Else
    bControlEmpty = True
    End If
    If Len(OperComboBox.Value) > 0 Then
    Cells(emptyRow, 4).Value = OperComboBox.Value
    Else
    bControlEmpty = True
    End If
    
If bControlEmpty Then
    MissingUserForm.Show
Else
    
    Cells(emptyRow, 5).Value = Date

    If StartOptionButton.Value = True Then
        Cells(emptyRow, 6).Value = Time
     Else
        Cells(emptyRow, 7).Value = Time
    End If

    ThisWorkbook.Save
           
    Sheet1.Protect Password:="2606"
    
 End If
Loop While bControlEmpty

Unload Me

End Sub