Set date format as dd-mmm-yyyy in activex textbox VBA

Question

Is there a way to set date format as dd-mmm-yyyy in ActiveX TextBox?

I'm using the code below to separate the quote but there is one error while user is putting month., i.e. 31-May-1993 but sometimes user is putting 31-may-2015.

Because of that unable to fetch the data through server...

Private Sub TextBox1_Change()
    If TextBox1.TextLength = 2 Or TextBox1.TextLength = 6 Then
    TextBox1.Text = TextBox1.Text + "-"
    End If
End Sub

Why are you using Textbox to accept dates? You may want to see [This](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba/12013961#12013961) — Siddharth Rout, May 26 '15 at 09:22
@SiddharthRout... i checked ur post but the problem is m unable to download the that form... — Kalpesh Koli, May 26 '15 at 09:37
Whenver i open that link it shows something went wrong.. or Internet Explorer cannot display the webpage — Kalpesh Koli, May 26 '15 at 09:49

score 3 · Accepted Answer · answered May 26 '15 at 10:27

Can I suggest you take a different approach with this and do something like (Note this is untested and not final code just a few suggestions for how you could handle this):

Private Sub Textbox1_LostFocus()
  Dim Da as Date
  Da = CDate(Textbox1.Text)
  Textbox1.Text = Format(Da, "dd-mmm-yyyy")
End Sub

This will:

Take whatever text is entered when the user clicks outside of the active box (LostFocus instead of running every key press)
Convert the value to a Date
Put back into the Textbox in the format that you require (dd-mmm-yyyy)

You could then add error handling in this so that if vba can't convert it to a date it could report to the user that they have entered an incorrect value and to correct it before proceeding.

I'm also not advocating that this is best practice but a way that the OP could get his method to work.

Set date format as dd-mmm-yyyy in activex textbox VBA

1 Answers1