Textboxes date format problem

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
84
Office Version
  1. 2016
Platform
  1. Windows
Dear MrExcel community,

I have a program for search & update. There are 32 column and almost 5 column are date. This dates writing on excel as "dd.mm.yyyy". But when I search a data dates comes to textboxs as "mm/dd/yyyy". But sometimes I don't know how, dates comes true format on textboxes. I need to dates must comes true format to textboxes all the time.

Thank you for your time and interest.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
VBA somehow will convert internally into the US format of mm/dd/yyyy. So, it is a pain to those not using the US format :giggle:

However, internally it stored as date value, doesn't care about the display format. Just remember to reformat it when wanting to display back.
 
Upvote 0
VBA somehow will convert internally into the US format of mm/dd/yyyy. So, it is a pain to those not using the US format :giggle:

However, internally it stored as date value, doesn't care about the display format. Just remember to reformat it when wanting to display back.
It’s not about only display format. When I update other datas, also date updated as wrong format. It is the problem :(
 
Upvote 0
I believe the problem is the program read the data from TextBox as text. It has no format as the pogram see it. The program has to be told how to read the data. If there are two different format mixed together, then it would be a challenge.
 
Upvote 0
I believe the problem is the program read the data from TextBox as text. It has no format as the pogram see it. The program has to be told how to read the data. If there are two different format mixed together, then it would be a challenge.
I don’t think so. Because on excel date format is “dd.mm.yyyy” but program read and convert as “mm/dd/yyyy”. If program read data as text it must not convert anything. I think there must be solution
 
Upvote 0
I don’t think so. Because on excel date format is “dd.mm.yyyy” but program read and convert as “mm/dd/yyyy”. If program read data as text it must not convert anything. I think there must be solution
It is like this:
Your Worksheet default format is "dd/mm/yyyy"

Say you have TextBox in UserForm. You enter date in same format as your Excel like "1/3/2021" (today date).

You want to write back to Sheet

VBA Code:
Private Sub CommandButton1_Click()
A = CDate(Me.TextBox1)
Range("C3") = Format(A, "dd/mm/yyyy")
End Sub

VBA will read "1" as month, ""3" as day and "2021" as year. So, what you see in Range("C3") would be 3/1/2021 to comply with your worksheet formatting.
 
Upvote 0
It is like this:
Your Worksheet default format is "dd/mm/yyyy"

Say you have TextBox in UserForm. You enter date in same format as your Excel like "1/3/2021" (today date).

You want to write back to Sheet

VBA Code:
Private Sub CommandButton1_Click()
A = CDate(Me.TextBox1)
Range("C3") = Format(A, "dd/mm/yyyy")
End Sub

VBA will read "1" as month, ""3" as day and "2021" as year. So, what you see in Range("C3") would be 3/1/2021 to comply with your worksheet formatting.
Exactly but Im not working with todays date. This codes writes textbox todays date
 
Upvote 0
Your Worksheet default format is "dd/mm/yyyy"

Say you have TextBox in UserForm. You enter date in same format as your Excel like "1/3/2021" (today date).

You want to write back to Sheet

VBA Code:
Private Sub CommandButton1_Click()
A = CDate(Me.TextBox1)
Range("C3") = Format(A, "dd/mm/yyyy")
End Sub

VBA will read "1" as month, ""3" as day and "2021" as year. So, what you see in Range("C3") would be 3/1/2021 to comply with your worksheet formatting.
I solved. That was easy.
VBA Code:
Private Sub txtT_Change()
txtT.Text = Format(txtT, “dd.mm.yyyy”)
End Sub
Thank you for your interest!
 
Upvote 0
Solution

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top