Validation Code Problem

[cpounds217]

Ok so I have a 2 page Userform when the first page contains textboxes, comboboxes, and dropdowns, and the second has a listbox, textboxes, and buttons. After data is put in, the userform (current version being used) unloads. The problem (on a user-friendly) standpoint, is that it is a mail log, with potentially hundreds of entries per day. I was trying to speed up the process, by using the click event of the enter button to insert the data, and then reload the form, but the validation code for the first textbox was tripped. Now if I remove the “SetFocus” property, the validation code isn’t tripped, but like I said this is about making the form even more User-Friendly, so I need the SetFocus property. Now we could just say ignore the validation, but I use “rgbPink” for validation, along with caption changes. This is can intrusive when you reload the form and expect a fresh start. I also want to maintain the validation code as is because this is all pertinent data.

Below is the code that pertains to the issue:

Private Sub EnterButton_Click()
 
    If Not EHSShowError Then Exit Sub
   
    Call AddDataToList
    If MsgBox("Do you have another Tier II to log?", vbYesNo) = vbYes Then
        Tier2Pages.Value = 0
        DateSubmitted.Value = ""
        BusinessName.Value = ""
        Address.Value = ""
        ZipCode.Value = ""
        County.Value = ""
        SubjectComboBox.Value = ""
        SelectedEHSList.Value = ""
        DateSubmitted.SetFocus
    Else
        Unload Me
        wsMenu.Activate
        wsMenu.Select
    End If
   
End Sub
 
Private Sub AddDataToList()
   
    Dim LastRow As Long
    Dim ws As Worksheet
   
    Set ws = wsTierII
   
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
   
    ws.Range("A" & LastRow).Value = DateSubmitted.Value
    ws.Range("A" & LastRow).Offset(0, 1).Value = BusinessName.Value
    ws.Range("A" & LastRow).Offset(0, 2).Value = Address.Value
    ws.Range("A" & LastRow).Offset(0, 3).Value = City.Value
    ws.Range("A" & LastRow).Offset(0, 4).Value = State.Value
    ws.Range("A" & LastRow).Offset(0, 5).Value = ZipCode.Value
    ws.Range("A" & LastRow).Offset(0, 6).Value = County.Value
    ws.Range("A" & LastRow).Offset(0, 7).Value = SubjectComboBox.Value
    ws.Range("A" & LastRow).Offset(0, 8).Value = SelectedEHSList.Value
    ws.Range("A" & LastRow).Offset(0, 9).Value = YesOrNo(ePlanCheckbox.Value)
    ws.Range("A" & LastRow).Offset(0, 10).Value = DateFiled.Value
   
    Application.ScreenUpdating = True
   
    wsTierII.Activate
    wsTierII.Select
   
    MsgBox BusinessName.Value & " was added to Tier II Log!"
   
End Sub
 
Private Sub DateSubmitted_AfterUpdate()
 
    If DateSubmitted.Value <> "" Then
        DateSubmitted.BackColor = rgbWhite
        DateSubmittedLabel.ForeColor = Me.ForeColor
        DateSubmittedLabel.Caption = "Date Submitted"
    End If
   
    If IsDate(DateSubmitted.Value) Then
        DateSubmitted.Value = Format(CDate(DateSubmitted.Value), "M/D/YY")
    End If
   
End Sub
 
Private Sub DateSubmitted_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 
    Dim ActualDate As Date
   
    If Not IsDate(DateSubmitted.Value) Then
        Call DateSubmittedShowError
        Cancel = True
    Else
        ActualDate = CDate(DateSubmitted.Value)
       
        If ActualDate >= Date Then
            Call DateSubmittedShowError
            Cancel = True
        End If
    End If
   
End Sub
 
Private Sub DateSubmittedShowError()
 
    DateSubmitted.BackColor = rgbPink
    DateSubmittedLabel.ForeColor = rgbRed
    DateSubmittedLabel.Caption = "Submission Date on Form"
    DateSubmitted.SelStart = 0
    DateSubmitted.SelLength = Len(DateSubmitted.Value)
 
End Sub

[Paul_Hossler]

1. I added CODE tags for you -- use the [#] icon to insert them and them paste your macro between

2. Might be typo but you're missing some DOTs below. Same in DateSubmitted_AfterUpdate()

Private Sub DateSubmittedShowError() 
     
    DateSubmitted.BackColor = rgbPink 
    DateSubmittedDOTLabel.ForeColor = rgbRed 
    DateSubmittedDOTLabel.Caption = "Submission Date on Form" 
    DateSubmitted.SelStart = 0 
    DateSubmitted.SelLength = Len(DateSubmitted.Value) 
     
End Sub

3. You can save some typing and make it a a little easier to read by using With ... End With

Private Sub DateSubmittedShowError() 
     
    With DateSubmitted.
        .BackColor = rgbPink 
        .Label.ForeColor = rgbRed 
        .Label.Caption = "Submission Date on Form" 
         .SelStart = 0 
          .SelLength = Len(DateSubmitted.Value) 
    End With
     
End Sub

[cpounds217]

No dots were missing. My labels are named DateSubmittedLabel. And thanks for the tip on how to insert code, and not have it look haphazard.

[Paul_Hossler]

No dots were missing. My labels are named DateSubmittedLabel.

Yea, I didn't look close enough

[cpounds217]

Does anyone have any other input on this?

[mikerickson]

Have you tried to set the .Enabled property of the Enter button to false until all the data is validated as OK?

[cpounds217]

It wasn’t a problem with the enter button itself. It was the validation was activated when the userform would auto-reload. So my current solve has been to add the validation to the enter button click and remove it from the BeforeUpdate sub. This is working for now, so unless, someone has a better suggestion, I would say this can be marked as solve.

Thanks for all the help and suggestions, though!