cpounds217
02-02-2018, 02:46 PM
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
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