Chronological Dates In TextBoxes On UserForm

[yoshi_5]

Hello,

I have 20 TextBoxes on a UserForm.

How would I loop through the textboxes to ensure that the current textbox date value is not LESS THAN the previous textbox and is not GREATER THAN the textbox after it.

I am already comfortable using CDate in my visual basic code to check other dates in other forms, but I'm hoping there is an easy way to loop through all of them on this particular form and display an error if and when the LESS THAN or GREATER THAN are true as mentioned in my example above.

I am also already aware that the first TextBox will have no previous date to check against as well as the last textbox having no next textbox to check against, I can easily work around those things.

The current textbox can be EQUAL to either the previous textbox or the next textbox.

The textboxes are named in order, I am looking at looping through TextBox1 through TextBox20 to do this.

Thanks to anyone in advance who can point me in the right direction or get me started.

[mikerickson]

This will tell you what order the text box values are in.

Note the comment with the meaning of lngOrder at that point.

Please Login or Register  to view this content.

[yoshi_5]

Hi mikeerickson,

First - thank you for your assistance. However, it doesn't appear to be working as expected...

My textboxes all have dates in them.

TextBox1 date = 6/27/2015
TextBox2 date = 6/29/2015
TextBox3 date = 6/28/2015
TextBox4 through TextBox20 date = 6/29/2015

I am hoping because TextBox3 date is less than TextBox2 that I would get the "not ascending" message, but I am getting the "Textboxes are (weakly) ascending" message.

Can you suggest a work-around? Also, what code would you add to prevent Type Mismatch error if the selected TextBox happens to be empty? But the code would still move to the next TextBox to perform the check?

I hope I've explained myself well.

Thank you again!

[mikerickson]

In my testing, I got "not ascending". Perhaps the spaces around the / are interfering.

Or possibly what you thought was TextBox2 isn't. (In testing I found that copying groups of text boxes results in unreliable ordering of text box names. i.e. if TB1 is above TB2 is above...is above TB8, grouping, copying and pasting might result in TB9 above TB10 above TB11 above TB12 above TB16 above TB15...

[yoshi_5]

Hello,

Thanks for attaching the workbook. I was just stumped and having your code working in front of me versus looking at the code in the thread helped a bunch. Unfortunately, your code was good all along and a simple 1 where a 0 should have been was my own downfall. It seems to be working great now...

However, can you help with the second part of my question? Or do I need to mark this thread as solved and open another? I don't post often, so I'm not sure about the rules.

How would I handle the below situation:

TextBox1 = 6/29/2015
TextBox2 = 7/2/2015
TextBox3 = BLANK
TextBox4 = 7/4/2015

I would like the above to show as "ascending" and always show ascending no matter where or how many BLANK textboxes there are on the UserForm as long as all date entries are truly ascending.

TextBox1 = 6/29/2015
TextBox2 = BLANK
TextBox3 = 6/27/2015
TextBox4 = 7/1/2015

The second example of course would show as not ascending, and same as above I'd like to make sure I'm looking no matter how many or no matter where any of the blank textboxes might be as long as the date entries are truly descending.

I am as you expect getting a "Type Mismatch" error as CDate does not play well with blanks.

You've been a fantastic help, thanks so much!

[mikerickson]

Add this check

Please Login or Register  to view this content.

[yoshi_5]

Hi - Your check appears to only work for textboxes that are one after another when looking at blanks and doing the check.

I have this currently:

TextBox1 = 6/27/2015
TextBox2 = 6/29/2015
TextBox3 through TextBox10 = BLANK
TextBox11 = 6/28/2015 <-- Not in ascending order
TextBox12 through TextBox20 = BLANK

Because TextBox11 (more importantly the first non-blank) after TextBox2 date is before I should be getting the "Non-Ascending", but instead I get the "ascending" message.

Sorry if my first explanation wasn't clear.

[mikerickson]

This should deal with blank (or any non-date enteries.

Please Login or Register  to view this content.