Text box validation problems

**paxtrodamus** · 05-31-2013, 05:43 PM

HELP. I need to make sure textbox entries follow certain rules so that values passed to a modules doesn't crash while calculating. Textbox entries must be positive numbers and must not conatain any alpha characters or invalid characters such as blanks, symbols, etc. When tabbing to the next textbox, the textbox needs to be tested and if invalid entries are found then focus needs to be put back on that textbox until a valid number is entered. Not all of the message box calls are in their final form. I've been having problems getting some of the message boxes to close and return to the textboxes for new entries. (See below, attached .xlsm file might make more sense; 1st tab explains validation rules)

**************************************************************************************************************
**************************************************************************************************************

Please Login or Register  to view this content.

**FDibbins** · 05-31-2013, 08:40 PM

Interesting...this is pretty much the same post you made 8 months ago?

http://www.excelforum.com/excel-prog...alidation.html

Also, I have closed the other 2 duplicate threads you posted, you may continue on this 1

**jaslake** · 06-01-2013, 04:45 PM

Hi paxtrodamus

The Code in the attached appears to do as you require. You'll need to do this

Not all of the message box calls are in their final form

Let me know of issues...

**paxtrodamus** · 06-03-2013, 12:58 PM

Moderator,

The reason there were multiple posts was because I forgot to add tags. When I tried to edit my post it wouldn't let me edit the search tags so the only option I saw was to repost. Also, I didn't have any responses for 8 months so I thought I would try again.

**paxtrodamus** · 06-03-2013, 06:08 PM

John,

Its not really an issue, but for the life of me I can't figure out in Module2 what is being returned from the function. Its a Boolean, but NumberVal doesn't show up anywhere after it is called. The function is being called from this Private Sub

Please Login or Register  to view this content.

It looks like operationally the NumberVal function toggles the Public Numeric between TRUE & FALSE. If that's so why must the Function NumberVal be declared as Boolean? Is there a reason? Sorry, as you can tell from my code which doesn't want to work, I'm by no means an expert at all the ins & outs of VBA. Your code works with a third of the code I had to use. Your function is below

Please Login or Register  to view this content.

**jaslake** · 06-03-2013, 10:26 PM

Hi paxtrodamus

It's a bit late today...perhaps tomorrow I'll get into the confusion...and I think I understand the confusion...perhaps...maybe...

**FDibbins** · 06-03-2013, 10:49 PM

Hi paxtrodamus

To edit your post (to add tags or change text etc, click the "edit post" button at the bottom right of that post

**paxtrodamus** · 06-04-2013, 05:52 PM

Hi John,

I went old school and drew a flow chart of how I think your textbox validation function works, like I was taught when I was a wee young boy (see attached pdf and see if you agree). From further reading it appears functions don't always have to return values. I'm ashamed to say that I never considered using the AfterUpdate event

. I used Exit events but had problems. This is the best solution I've come across for textbox validation. I never thought of Mid/InStr/While combination. This will help me with my hydrology project at work.

Paxton

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**jaslake** · 06-04-2013, 06:58 PM

Hi paxtrodamus

I'm also from the old school...retired CPA...have used Flow Charts extensively in system development. I'm a bit tired this evening...these 71 year old bones can't handle the beating they've taken these past several days.

I'll try to look at it later this week...promise...have more physical work yet to do this week.

I've also had issues with Focus on Exit Events...AfterUpdate appears to solve that issue...at least in this case...it's all a bit squirrelly at times.

I assume the Code works for you? If so... please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks.