Date format in userform textbox

[hkbhansali]

I'm looking for a way to automatically format the date in a VBA text box via userform to a dd/mmm/yyyy format,
and I want it to format as the user is typing it in.
and also want the same text box to always pick the date from column "D" in dd/mmm/yyyy format but date should be editable.
(in my worksheet column "D" has same dd/mmm/yyyy format)
Thanks.

 

[gallen]

You give little information so I'll have to guess

assuming your textbox is called Textbox1 and your date is in cell D1, paste this into the code window of the userform:

(nb: you can't format the textbox as you type as it will always assume the current year. format it on lost focus)

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(TextBox1.Text) Then TextBox1.Text = Format(TextBox1.Text, "dd/mmm/yyyy")
End Sub


Private Sub UserForm_Initialize()
    'set the date from cell D1
    If IsDate(Sheet1.Range("D1").Value) Then TextBox1.Text = Format(Sheet1.Range("D1").Value, "dd/mmm/yyyy")
End Sub

 

[hkbhansali]

Hi, gallen
Thanks for reply.
my textbox is "txtdate" and date store in column "D" -("D2:D500).
your code can't display date in "dd/mmm/yyyy" format.

 

[gallen]

It was tested and worked fine on a userform textbox for me.

How does it determine which cell in column D to get the date from?

 

[mikerickson]

I don't understand what you mean by "fomat as the user is typing"


If I want to enter June 3, 2016, what should happen when I press the "J"?

If you want to change the format to yours after the user is done entering, gallen's code is the way to go.