Userform - Required Fields 2

[RP1America]

I would like to require all fields in a userform to be populated upon the "OK" click event. In addition, I would like one of these field to require a 4-digit input (no more, no less - as it is a year).

1) Is this done in the click event? I've been researching and have come accross something called Before Update, yet I do not understand it.

2) User fills out form, clicks "OK", one entry is not filled out, after the msgbox pops up, how do I take them back to the form to reenter data without going all the way through the original click event?

3) How do I set up my code to start from the top and go down...if the first field is not populated, then msgbox for that field comes up and goes back to form immeidately even if 2 or 3 more fields are not populated further downt the line. I do not want all messages to pop up, rather the first, then they can go correct, click "OK" again, then if they missed another, it would catch the next in line...

4) Also, how do I require the 4-digit year?

Here is the msgbox text for each field. This code is in no way complete, I just do not know how to get to what I am wanting quite yet.

Any ideas are greatly appreciated! Thanks!

If txtYear = "" Then MsgBox ("Please Enter Year")
If cboQuarter = "" Then MsgBox ("Please Select Quarter")
If txtRT11 = "%" Then MsgBox ("Please Value Commission Options 1 - 7 Percentage Rate")
If txtRT12 = "%" Then MsgBox ("Please Value Commission Options 8 - 11 Percentage Rate")
If txtRT13 = "%" Then MsgBox ("Please Value Commission Options 14, 16 Percentage Rate")
If txtRT14 = "%" Then MsgBox ("Please Value Commission Options 15, 17 Percentage Rate")
If txtRT15 = "%" Then MsgBox ("Please Value DCP Series I Percentage Rate")
If txtRT21 = "%" Then MsgBox ("Please Value Commission Options 1 - 7, DCP Patriot Select Percentage Rate")
If txtRT22 = "%" Then MsgBox ("Please Value Commission Options 8 - 11 Percentage Rate")
If txtRT23 = "%" Then MsgBox ("Please Value Commission Options 14, 16 Percentage Rate")
If txtRT24 = "%" Then MsgBox ("Please Value Commission Options 15, 17 Percentage Rate")
If txtRT25 = "%" Then MsgBox ("Please Value DCP Series I Percentage Rate")

 

[SkipVought]

"one of these field to require a 4-digit input (no more, no less - as it is a year)."

that is not a very specific edit for a year. 0000 is a 4-digit imput. 9999 is a 4-digit imput. You might want the year to be greater than some value and less than another value.

I'd set up a table that has the upper and lower limits for numeric values.
[tt]
TxtName Lower Upper
txtYear 2000 2050
...
[/tt]

The you could simply loop thru your controls and check textbox values against the table.

dim ctr as control, r as range

for each ctr in yourform.controls
  for each r in range([YourEditTableTexboxRange])
     if r.value = ctr.name then
        'now check the textbox value against the limits
        if ctr.value < r.offset(0,1).value then
           'the value is below the Lower limit
           'what do you want to do???
        end if

        if ctr.value > r.offset(0,2).value then
           'the value is above the Upper limit
           'what do you want to do???
        end if
     end if
  next
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

This is a classic case of error-trapping, and unfortunately there is no easy way around it. The problem is that on a userform, while you CAN can focus to start at a certain control (using TabIndex), there is nothing to stop a user from moving focus.

In other words, yes, they can fill in control#3, and control#4...and not fill in control#1.

You have to test each one, as you have done.

From a design perspective, one other option is to use the .Visible property of controls.

Control#1 is visible, others are NOT.

In the _Change event of control#1, if value is X (what you want), then the next control becomes Visible.

This only works is there is a reasonably consistent and logical flow to the data input. This works well if there are dependencies. That is, Control#3 depends on something being in Control#1. Therefore Control#3 only becomes visible (available) IF there is something in Conotrl#1.

Etc. etc. etc.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

Thanks Gerry. I have actually used that logic in a different userform project, yet I don't believe it has (as much) relevance for this project.

I have figured out what I need though. Came up with this and it works like a charm!

'require field input
If txtYear = "" Then
    frmProductCost.Hide
MsgBox "Please Enter Year", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtYear.SetFocus
ElseIf txtYear < "2000" Then
    frmProductCost.Hide
MsgBox "Please Enter Valid Year", 0, "Entry Not Valid"
    frmProductCost.Show
    txtYear.SetFocus
ElseIf txtYear > "3000" Then
    frmProductCost.Hide
MsgBox "Please Enter Valid Year", 0, "Entry Not Valid"
    frmProductCost.Show
    txtYear.SetFocus
ElseIf cboQuarter = "" Then
    frmProductCost.Hide
MsgBox "Please Select Quarter", 0, "Field Must Be Valued"
    frmProductCost.Show
    cboQuarter.SetFocus
ElseIf txtRT11 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier I: Commission Options 1 - 7 Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT11.SetFocus
ElseIf txtRT12 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier I: Commission Options 8 - 11 Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT12.SetFocus
ElseIf txtRT13 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier I: Commission Options 14, 16 Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT13.SetFocus
ElseIf txtRT14 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier I: Commission Options 15, 17 Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT14.SetFocus
ElseIf txtRT15 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier I: DCP Series I Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT15.SetFocus
ElseIf txtRT21 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier II: Commission Options 1 - 7, DCP Patriot Select Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT21.SetFocus
ElseIf txtRT22 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier II: Commission Options 8 - 11 Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT22.SetFocus
ElseIf txtRT23 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier II: Commission Options 14, 16 Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT23.SetFocus
ElseIf txtRT24 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier II: Commission Options 15, 17 Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT24.SetFocus
ElseIf txtRT25 = "%" Then
    frmProductCost.Hide
MsgBox "Please Value Tier II: DCP Series I Percentage Rate", 0, "Field Must Be Valued"
    frmProductCost.Show
    txtRT25.SetFocus
End If

 

[Andrzejek]

WOW! A lot of code.

For the 4 digit year - instead of the text box, why don't you give the user a drop down combo filed out already with valid years? You can display curent year as a default, too. Allow to choose instead of entering the year. No validation needed.

As far as allowing to click on the OK button - make the button Enabled = False at the start. In the _Change of your text boxes you may loop thru all textboxes and check the values - if all is OK, set Enabled = True so they can click on it.

Have fun.

---- Andy

 

[RP1America]

Hey, thanks Andy! I didn't even think of it like that. Your way is MUCH simpler than what I originally thought was needed. Kudos!

 

[fumei]

The problem of course is what if you have multiple things missing.

Say txtYear is "", AND txtTR23 <> "%"

ElseIf txtRT23 = "%"

If txtYear = "" Then you will get the result of that:

  frmProductCost.Hide
  MsgBox "Please Enter Year", 0, "Field Must Be Valued"
  frmProductCost.Show
  txtYear.SetFocus

but nothing will detect that txtRT23 is in error as well. Once the IF of txtYear is True, none of the other ElseIf execute.

Therefore, you will get the message, focus goes to txtYear...but txtRT23 is still in error. The process begins again, and only now will you get the error message re: txtRT23, and the re-focus.

Suppose, five of them are blank (or some error), you have to run through the error trapping process five separate times, and five separate messages and five separate and individual re-focusing.

In the _Change of your text boxes you may loop thru all textboxes and check the values - if all is OK, set Enabled = True so they can click on it.

Quite true, except if not all are OK - say four are wrong - WHICH four? Which one do you set as the re-focus? The "first" one? If they only "fix" two of the four, the process begins again, and you have the same issue. WHICH two are still in error?


This can be done (detecting which are in "error") and working to ensure they are corrected. However, it takes a lot of thinking and work. I have done userforms where I needed (because of legal reasons) absolutely rock-solid error-trapping. At least 70% of all the code on the userform was that error-trapping, and it took 400+ lines of code to get it.

Again though, most userforms do not require such rock-solid error-trapping because the number of controls is fairly low. However, IF you have a lot, AND some have real dependencies (requirements) for values from "previous" controls, then well-designed work-flow makes a huge difference.

Here is another example of design. And keeping it simple....

A MultiPage. There are TWO tabs - Client and Invoice Info.

IMPORTANT! It is critical to distinquish between the .Name and .Caption. The Name of the tab that shows as "Invoice Info" is "InvoiceInfo".

InvoiceInfo has .Visible set to False to start. The user does not see it. They only see the Client tab.

Again, keeping it simple. There is a checkbox on the Page1 (renamed as "Client"), as well as a commandbutton captioned "Next".

Private Sub cmdNext_Click()
   If CheckBox1.Value = True Then
      With MultiPage1
         .Pages.Item("InvoiceInfo").Visible = True
         .Value = 1  [COLOR=red]'  this puts focus on Page2[/color red]
      End With
   Else
      MsgBox "There is missing information on this tab."
   End If
End Sub

So, when you click "Next", it checks to see if Checkbox1 is checked.

If it is:

1. it makes Page2 (renamed as "InvoiceInfo") .Visible = True. The user can now see the tab.

2. sets focus to that tab, so the user can start working on ITS controls.

In other words, you can do all the error trapping you need on say five or six controls on the first page BEFORE you let the user move on the the next bunch of controls (on Page2).

The final OK button is not on the multipage.

Thus, you can "chunk-ify" your error-trapping.

Clicking the OK button can do this in a number of ways. The simplest way is just to check the visibility of the tabs on the multipage because you know the sequential pages can NOT be visible until they have passed the error-trapping for the previous page.

This is using the dependencies. If the controls of Page2 require that values of controls on Page1 are X (or whatever), then "chunk-ify" the error-trapping. Make sure all the controls on Page1 are valid until the user can even get to the controls on Page2.

And of course, if required (likely), if they forget and click the Main "OK", it can easily test to see if Page2 was indeed used, and if not, send the user there.

Or whatever.

Etc. etc. etc. There are a number of ways to deal with this, but as I have mentioned before, serious rock-solid error-trapping (that is, logic flow) is THE most difficult part of coding (IMO). Thinking clearly about how things really happen, is far more important than the actual code itself...or what one may think "should" happen.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

How would I set the Year to current as default using the system date? Now function? How does that work? I would want it to default to 2009 now, but at the change of each year I would want it to automatically default to that year. Any advice?

Also, how do I check to see if all fields are valued?

Use and If statement with AND?

Thanks!

 

[fumei]

Just a note in case some got confused...

      With MultiPage1
         .Pages.Item("InvoiceInfo").Visible = True
         .[b]Value = 1[/b]  '  this puts focus on Page2

The pages collection of a multipage is 0-based.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[Andrzejek]

To get the curent year:

Dim i As Integer

For i = Year(Now) - 10 To Year(Now) + 10
    Combo1.AddItem i
Next i

Combo1.Text = Year(Now)

It is good for 10 years prior to today and 10 years into the future.

Have fun.

---- Andy

 

[combo]

If it makes sense, you can validate text when the user exits control:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not TextBox1.Text Like "####" Then
    Cancel = True
    Msgbox "Please fill in ..."
    TextBox1.SetFocus
End If
End Sub

combo

 

[fumei]

True...but what if the the user never entered the control? It is blank (and is not supposed to be), but they never clicked into it. They "forgot". It is still an error, as it is not supposed to be blank.

But yes, true, and is legitimately one of the methods used for error-trapping.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry