Correct vba code for text boxes in user form

Hi All,

I am new on this forum and would please like some help with two aspects of a User Form that I am currently working on.

I am having problems coming up with the correct VBA codes for the ‘DATE’ text box and the ‘DESCRIPTION’ text box, to do what I wish to achieve. I am a newbie in VBA, and I am happy to learn more with the kind help of members of this forum.

1. The 'DATE' text box
The Date textbox requires users to key in the date of transaction. This and other entries on the userform are transferred to a database.

The vba code that I inserted is to ensure that if a user types in a date in any form, the date will automatically convert to ‘dd/mm/yyyy’. For example, for the date 13/04/2013 – if a user keys in either 13.4.13 (with foolstops), or 13 4 13 (with spaces), or 13/4/13 (with forward slashes), and presses the tab key, the date immediately converts to 13/04/2013.

However, I have observed a problem: if, in error, someone keys into the date box, say, 113.4.13 (with foolstops), or 113 4 13 (with spaces), the code converts the date to 13/04/0113. This result is not a date, and I can’t figure out how to solve this problem.

I have appended below all the date-related codes that I use in the userform. I should be grateful if someone could, please, adjust the code for me, so that I don’t have that problem.

Please Login or Register  to view this content.

2.The ‘DESCRIPTION’ text box
For the ‘Description’ textbox, I used the ‘If IsNumeric …’ code to ensure that the box does not contain only NUMBERS. It should contain text or a combination of text and numbers – but never only numbers.

However, using the code below, I find that if, for example, I type into the Description box, say, 122345, I get a warning message - which is OK. However, if there is a space splitting the number into two, for example 12 2345, the code accepts it and would transfer it to the database even though it is all numbers and I don't want that to happen. I have appended below the code that I use for this, and should also be grateful for help to adjust the code so as to prevent this.

Please Login or Register  to view this content.

Thank you all in anticipation of your kind help.

newqueen

You should be using a date picker to insert dates. These are available through the Usermodule toolbox by selecting the insert other button.

With the Description text box.

write a short piece of code under the textbox change event, to delete any spaces in the textbox.

something like: [ I do not have access to excel here, so bear with me. ]

Please Login or Register  to view this content.

Dear Mehmetcik,

Thank you very much for your prompt and kind response to my Post. I am also grateful to you for taking the trouble to create a sample userform for me so as to help me understand better. I have learnt a great deal from that.

Re: the Date text box:
The date picker approach you suggested is very good. I would have adopted it, BUT for the diverse nature of the date of the transactions that we have to key into the userform – and also considering that, at each time, we have very many transactions with such diverse dates to key in.

A user could have an invoice (or document) with the date, e.g. 12/4/2013, and a second invoice with 20/9/2011, and a third with 16/7/2012, all different years and months.

Instead of having to scroll through the drop-down calendar for each month and year, I was of the opinion that it would be quicker to just type into the userform date box either 12/4/13 or 20/9/11 or16/7/12 etc, and in each case – after pressing the tab key - the date that will automatically appear in the date text box will be in the format ‘dd/mm/yyyy’, and correctly for each year, without the sort of problem I indicated in my first Post.

It was with a view to achieving such ‘quickness’ and ease that I sought to use the code in my first Post. I am hoping that there could be an adjustment to my ‘date’ code that would prevent the bugs that I have with it. The code works well for some calendar years but not for all calendar years. Example –

If I type in 13/4/13 it converts to 13/04/2013
If I type in 13/4/2 it converts to 13/04/2002
If I type in 13/4/29 it converts to 13/04/2029

BUT

If I type in 13/4/30 it converts to 13/04/1930
If I type in 13/4/44 it converts to 13/04/1944

(Anything beyond ‘29’ converts the date to the ‘nineteen hundreds’.

The second problem with the code is that, if a user makes a mistake and keys in say, 13/4/123 (instead of 13/4/13), it converts it to 13/04/0123.

Re: the Description text box
I am happy to say that the code you provided for this is EXACTLY what I need. Thanks very much. You’ve been very wonderful.

I am hoping that you will, please, find time to help me further and kindly have another look at the Date text box issue. I believe that, with your knowledge and expertise, a further closer look at my original code would detect what is causing the problem. Or better still, there might be a different code that will help me to achieve what I desire with respect to the Date box issue.

Kind Regards.

Clare

Clare

Users can type the date into a DatePicker control.

Thanks Norie for pointing this out to me. I tried it and saw that it is possible for a user to type a date into the DatePicker control.

There is something though that I discovered. I tried it a few times to be sure it works OK: After typing in the DATE and entering data in the other text boxes that follow, and clicking the 'ADD' button, the focus goes back to the DATE box. But I find that it is not the 'day' section that is highlighted for a new date entry. The number of times that I have tested it in the last 20 minutes, I find that it is either the 'month' section or the 'year' section that is highlighted.

With any one of those two highlited, when I start typing a new day, it is either the month or year that get's changed first. When I checked the code which Mehmetcit kindly put together for me, I wondered if there was anything missing from the code that would not make the highlight go straight back to the 'day' section when the focus goes back to the DATE box.

I am appending below the code so you or someone could, please, check why that might be happening.

Please Login or Register  to view this content.

Thanks to you, and to Mehmetcit and indeed to all other members of the forum for your continued help. I am very grateful.

Clare

Clare

Why would you start typing if day in the month or year part of the date picker?

All you need to do to move back to the day is use ←(left) or →(right).

Clare

I wasn't talking about moving through the calendar, I was takling about moving the focus.

All it needs is a couple of button presses for the user to return focus to the day section.

I've actually worked in high speed data entry and that sort of thing eventually comes second nature.

Also, I've found no way to set the focus to a particular selection in the inputbox part of a datepicker.

With a texbox you can change the selection with various properties like SelStart and SelLength, a datepicker doesn't have those properties.

Clare

The only way the calendar will appear is if the user clicks the down arrow, it won't appear if the click in the input part of the date picker.

If you don't want the calendar to appear at all then set the UpDown option in Custom properties.

That will change the dropdown arrow to a spinner type thing.

Clare

Perhaps you should start a new thread for that.

Might be an idea to upload an example workbook so we can see where you are and how things are organised in your database.

Hi Clare

I think this is what you need.

If you look at the userform, I have created a few text boxes to demonstrate how to use the userform.

Text box one is where you to type the dates into.

The Textbox Change routine ensures that only numbers are entered,

after you have entered 6 digits the change box moves the focus to text box three,

this is similar to pressing tab, but saves you a keypress,

this also causes the textbox exit routine to run

the textbox exit routine converts your typed data into

two digit day, two digit month and 4 digit year

it also checks to ensure the month is < 12

and that the number of days is valid. Try typing 300212 [ Feb 30 ] 0r 311112 [ 31st Nov ]. It works for all dates and handles leap years.

This date is converted to a serial date and the DatePicker is set to that date.

I think that this is what you are after. So enjoy.