Userform Textbox Formatting

[SaraO]

Hi,

I have a userform with a lot of textoxes that save and update data to a closed workbook, some are dates. I have set the date columns to a Custom format of "mmm dd, yyy". The date will not format correctly till I double click the cells. I have thousands of rows with dates, I don't have the time to always go back and double click on all the cells.

Is there a way to either set the texboxes to save the date in the proper format or an add-on to the code to format the column?

Everything I have tried so far has failed

 

[Akuini]

This will format textbox1 with the proper date format when you exit textbox1.

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] TextBox1_Exit([COLOR=Royalblue]ByVal[/COLOR] Cancel [COLOR=Royalblue]As[/COLOR] MSForms.ReturnBoolean)
    [COLOR=Royalblue]If[/COLOR] IsDate(TextBox1.Value) [COLOR=Royalblue]Then[/COLOR]
    TextBox1.Value = Format(TextBox1.Value, [COLOR=brown]"mmm dd, yyy"[/COLOR])
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

But if you have many textboxes with date then we might use Loop to do that, but we will need the list of the textboxes name.

 

[SaraO]

Thank you for your help.

I have 4 textboxes with dates. They are named Emp12, Emp58, Emp71 and Emp103.

 

[Akuini]

Well, since it is only 4, you need for 4 sub like this:
Put it the useform code module.
We can also use Loop to do the same job but see if this suit your need.
Note: the date format for year, is it "yyy" or "yyyy"?

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Emp12_Exit([COLOR=Royalblue]ByVal[/COLOR] Cancel [COLOR=Royalblue]As[/COLOR] MSForms.ReturnBoolean)
    [COLOR=Royalblue]With[/COLOR] Emp12
    [COLOR=Royalblue]If[/COLOR] IsDate(.Value) [COLOR=Royalblue]Then[/COLOR]
    .Value = Format(.Value, [COLOR=brown]"mmm dd, yyyy"[/COLOR])
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Emp58_Exit([COLOR=Royalblue]ByVal[/COLOR] Cancel [COLOR=Royalblue]As[/COLOR] MSForms.ReturnBoolean)
    [COLOR=Royalblue]With[/COLOR] Emp58
    [COLOR=Royalblue]If[/COLOR] IsDate(.Value) [COLOR=Royalblue]Then[/COLOR]
    .Value = Format(.Value, [COLOR=brown]"mmm dd, yyyy"[/COLOR])
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Emp71_Exit([COLOR=Royalblue]ByVal[/COLOR] Cancel [COLOR=Royalblue]As[/COLOR] MSForms.ReturnBoolean)
    [COLOR=Royalblue]With[/COLOR] Emp71 
    [COLOR=Royalblue]If[/COLOR] IsDate(.Value) [COLOR=Royalblue]Then[/COLOR]
    .Value = Format(.Value, [COLOR=brown]"mmm dd, yyyy"[/COLOR])
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Emp103_Exit([COLOR=Royalblue]ByVal[/COLOR] Cancel [COLOR=Royalblue]As[/COLOR] MSForms.ReturnBoolean)
    [COLOR=Royalblue]With[/COLOR] Emp103
    [COLOR=Royalblue]If[/COLOR] IsDate(.Value) [COLOR=Royalblue]Then[/COLOR]
    .Value = Format(.Value, [COLOR=brown]"mmm dd, yyyy"[/COLOR])
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[DanteAmor]

Consider that in the textbox you store the information as text, when you pass the data to the cell it is passed as text.
Then you must convert that text to date.

Eg.

Private Sub CommandButton1_Click()
    Range("E6").Value = CDate(Emp12.Value)
End Sub