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!