• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Form items: How to change the format of text, number & date?

inddon

Member
Hello there,

I have created my first VBA Form in excel and have placed the text items. I would like how to do format the items in the form:

1. All text items to have alpha-numeric
2. All numeric items to have numeric
3. All date items to have date format as "DD-Mmm-YYYY"

Looking forward to hearing from you.

Thank & greetings
Don
 
Hello there,

I have created my first VBA Form in excel and have placed the text items. I would like how to do format the items in the form:

1. All text items to have alpha-numeric
2. All numeric items to have numeric
3. All date items to have date format as "DD-Mmm-YYYY"

Looking forward to hearing from you.

Thank & greetings
Don
Hi,

Is this a userform or do you have textboxes, other objects on the worksheet? It would really help to see your workbook.
 
Not completely sure what you mean. In XL, whether dealing with cells, VB variables, or text boxes, data is data. By that I mean, a value is stored as it naturally is, we can just change the format of how it appears. For your text boxes, user can input whatever they want. You would need to write code taking that input and checking to see if it's a valid text/number/date.

The good news/bad news is that user can input their date in whatever format they personally prefer, and XL will store it as a Long/Date value. The other option with choosing dates is to use the Microsoft Date and Time Picker. If you don't have it in your toolbox, right-click on Toolbox, Additional Controls, and scroll down to find it. The Date picker lets you use a nice calendar control, and has additional benefit of letting you use a built-in date format, or definining a custom one.
upload_2014-8-26_15-51-24.png
 
Hi,

Is this a userform or do you have textboxes, other objects on the worksheet? It would really help to see your workbook.


Hello Mike,

Thanks for your quick reply.

Attached is the file I am working on

Looking forward to hearing from you

Thanks
Don
 

Attachments

  • User Form - Data Entry.xlsm
    25 KB · Views: 4
W
Not completely sure what you mean. In XL, whether dealing with cells, VB variables, or text boxes, data is data. By that I mean, a value is stored as it naturally is, we can just change the format of how it appears. For your text boxes, user can input whatever they want. You would need to write code taking that input and checking to see if it's a valid text/number/date.

The good news/bad news is that user can input their date in whatever format they personally prefer, and XL will store it as a Long/Date value. The other option with choosing dates is to use the Microsoft Date and Time Picker. If you don't have it in your toolbox, right-click on Toolbox, Additional Controls, and scroll down to find it. The Date picker lets you use a nice calendar control, and has additional benefit of letting you use a built-in date format, or definining a custom one.
View attachment 9820

Hi Luke,

Thank you for your reply.

When I click on Additional control it just doesn't show up anything

Please advice.


As you mentioned, the user can enter anything in the item and the code has to see if the entered value is valid text/number/date. Do you have any example code for this?


I have uploaded the file for your reference.

Looking forward to hearing from you

Thanks
Don
 

Attachments

  • User Form - Data Entry.xlsm
    25 KB · Views: 2
Hello Mike,

Thanks for your quick reply.

Attached is the file I am working on

Looking forward to hearing from you

Thanks
Don
Hi,

Textboxes return text so there are no issues with any of your text values the issues are with dates and telephone numbers. For dates simply chage this line

Cells(eRow, 4).Value = txtDate.Text

to this

Cells(eRow, 4).Value = CDate(txtDate.Text)

All phone No's I have experience of begin with a zero and to preserve that I suggest you store your telephone numbers as text because your not likely to want to do any mathematical operations on them so change this line

Cells(eRow, 3).Value = txtMobile.Text

to these lines

With Cells(eRow, 3)
.NumberFormat = "@"
.Value = txtMobile.Text
End With

I uploaded a working version
 

Attachments

  • User Form - Data Entry.xlsm
    22.4 KB · Views: 9
Not completely sure what you mean. In XL, whether dealing with cells, VB variables, or text boxes, data is data. By that I mean, a value is stored as it naturally is, we can just change the format of how it appears. For your text boxes, user can input whatever they want. You would need to write code taking that input and checking to see if it's a valid text/number/date.

The good news/bad news is that user can input their date in whatever format they personally prefer, and XL will store it as a Long/Date value. The other option with choosing dates is to use the Microsoft Date and Time Picker. If you don't have it in your toolbox, right-click on Toolbox, Additional Controls, and scroll down to find it. The Date picker lets you use a nice calendar control, and has additional benefit of letting you use a built-in date format, or definining a custom one.
View attachment 9820

Hello Luke,

I have activated the date picker. I changed the property values shown in your attached picture.
Format: 3 - dtpCustom
CUstomFormat: DD-MM-YYYY

On selecting a date from the date picker, I pick up a date in the month of August,
the return value is a vague number returned on the date picker field like
DD-08-YYYY. If I select a date in June then it displays as DD-06-YYYY


Could you please advice


Thanks
Don
 
Note that in my picture, I have 3 M's. :)
dd-MMM-yyyy
Capitalization also matters for the M's.
 
Back
Top