Userform help

[MWhiteDesigns]

Good afternoon,
Below is the code i am using for my userform. It works fine except for one issue. What it is suppose to do is prompt an error message if a field is left blank when you click submit, then when you click ok, it lets you correct the issue. Once everything is filled out correctly and you click submit, it automatically renames the file and sends an email.

Right now, it shows the error messages if the fields are left blank when you click submit, however it still sends the email even though its not complete

I need for it to populate the error message but NOT send the email until everything is completed. I have a feeling there are some If ends misplaced but i dont know where adjust.

Private Sub SubmitCommand_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Sheets("HVS Attrition")

With ws


.Cells(5, 3).Value = NameTextBox.Value
If NameTextBox.Value = "" Then
MsgBox "Name cannot be blank.", , "Name error"
Else
.Cells(9, 3) = EIDTextBox.Value
If EIDTextBox.Value = "" Then
MsgBox "EID cannot be blank.", , "EID error"
Else
.Cells(13, 3) = AVAYATextBox.Value
If AVAYATextBox.Value = "" Then
MsgBox "AVAYA IP cannot be blank.", , "AVAYA IP error"
Else
.Cells(17, 3) = DepartmentComboBox.Value
If DepartmentComboBox.Value = "Please Choose..." Then
MsgBox "Department must be selected.", , "Department selection error"
Else
.Cells(19, 3) = EffectiveDateTextBox.Value
If EffectiveDateTextBox.Value = "" Then
MsgBox "Effective Date cannot be blank.", , "Effective Date error"
Else
.Cells(21, 3) = ReasonComboBox.Value
If ReasonComboBox.Value = "Please Choose..." Then
MsgBox "Reason for Attrition must be selected.", , "Reason error"
Else
.Cells(24, 3) = ReportedByTextBox.Value
If ReportedByTextBox.Value = "" Then
MsgBox "Please specify person reporting Attrition", , "Report By error"
Else
.Cells(20, 5).Value = NotesTextBox.Value
If NotesTextBox.Value = "Please specify reason for attrition..." Then
MsgBox "Please specify reason for Attrition", , "Reason Error"
End If
End If
End If
End If
End If
End If
End If
End If

If Me.YesOptionButton.Value = True Then
.Cells(27, 3) = "Yes"
End If
If Me.NoOptionButton.Value = True Then
.Cells(27, 3) = "No"
End If
If Me.SundayCheckBox.Value = True Then
.Cells(5, 7) = "X"
End If
If Me.MondayCheckBox.Value = True Then
.Cells(7, 7) = "X"
End If
If Me.TuesdayCheckBox.Value = True Then
.Cells(9, 7) = "X"
End If
If Me.WednesdayCheckBox.Value = True Then
.Cells(11, 7) = "X"
End If
If Me.ThursdayCheckBox.Value = True Then
.Cells(13, 7) = "X"
End If
If Me.FridayCheckBox.Value = True Then
.Cells(15, 7) = "X"
End If
If Me.SaturdayCheckBox.Value = True Then
.Cells(17, 7) = "X"
End If

.Cells(5, 11).Value = SundayStartTextBox.Value
.Cells(5, 14).Value = SundayEndTextBox.Value

.Cells(7, 11).Value = MondayStartTextBox.Value
.Cells(7, 14).Value = MondayEndTextBox.Value

.Cells(9, 11).Value = TuesdayStartTextBox.Value
.Cells(9, 14).Value = TuesdayEndTextBox.Value

.Cells(11, 11).Value = WednesdayStartTextBox.Value
.Cells(11, 14).Value = WednesdayEndTextBox.Value

.Cells(13, 11).Value = ThursdayStartTextBox.Value
.Cells(13, 14).Value = ThursdayEndTextBox.Value

.Cells(15, 11).Value = FridayStartTextBox.Value
.Cells(15, 14).Value = FridayEndTextBox.Value

.Cells(17, 11).Value = SaturdayStartTextBox.Value
.Cells(17, 14).Value = SaturdayEndTextBox.Value



End With

Call Mail_ActiveSheet
End Sub

 

[ChrisM]

You haven't put in any code to actually stop the macro when any of your data checks fail. You are telling the user they have an invalid value, but you need to stop the program at the same time.

Question: You are writing the values to the worksheet, even if the user enters a bad value. Is this what you want? When I've done this in the past, I won't write anything to the sheet until all data is good.

I would restructure like this:

Private Sub SubmitCommand_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Sheets("HVS Attrition")

With ws

If NameTextBox.Value = "" Then
MsgBox "Name cannot be blank.", , "Name error"
Exit Sub
End If

If EIDTextBox.Value = "" Then
MsgBox "EID cannot be blank.", , "EID error"
Exit Sub
End If

If AVAYATextBox.Value = "" Then
MsgBox "AVAYA IP cannot be blank.", , "AVAYA IP error"
Exit Sub
End If

'repeat
..
..

'Now that all data verified write values to sheet and email:

.Cells(5, 3).Value = NameTextBox.Value
.Cells(9, 3) = EIDTextBox.Value
.Cells(13, 3) = AVAYATextBox.Value
..
..
Call Mail_ActiveSheet

 

[MWhiteDesigns]

Chris you are the man! Thank you so much for the help. Works like a charm. Last question.

I am also using a frame to house the associate schedule. I just want to have a message pop up if there is nothing in ANY of the fields. Is this possible?

 

[ChrisM]

If you don't have a lot of controls in the frame, the easiest way is just a nested loop like you had originally:

If text1.text = "" then
    if text2.text = "" then
         if text3.text = "" then
              msgbox "need to enter data!"
              exit sub
         end if
    end if
end if

If you have a lot of controls, then we could discuss looping through them automatically, but the approach above is the easiest.