Formatting a date from a textbox

Hello. I'm trying to format the date entry in a textbox on a form so that when a future date is entered via the form onto the relevant cell in the active sheet, it will always show in the following format, '01/mm/yyyy' in the cell. The first day of the month should always be 01 and there should be slashes showing. So no matter how the user enters the date in the textbox (whether they use the '/' or not, and/or put the day other than 01) it will always override with the correct format into the cell. The textbox is always initialized to show '01/mm/yyyy' which the user will type over. I have tried a couple of ways but can't get it to work properly:

This is the line of code I'm working from, where textbox3 is the place the date is entered:

If OptionButton5 Then Cells(Nextrow, 3) = TextBox3.Text

I have tried using:
TextBox3.Text = format(textBox3.Text, "01/mm/yyyy")
which just literally adds 01/mm/yyyy into the box after the textbox text

and I've tried:

With TextBox3
Select Case Len(.Text)
Case 2, 5
.Text = .Text & "/"
End Select

If InStr(.Text, "//") Then
.Text = Replace(.Text, "//", "/")
End If
End With

which doesn't do anything. I have also considered using the calender but this isn't suitable on this occasion. I would appreciate help with this, thanks!

Try

TextBox3.Text = "01/" & format(textBox3.Text, "mm/yyyy")


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Blondegirl" <[email protected]> wrote
in message news:[email protected]...
>
> Hello. I'm trying to format the date entry in a textbox on a form so
> that when a future date is entered via the form onto the relevant cell
> in the active sheet, it will always show in the following format,
> '01/mm/yyyy' in the cell. The first day of the month should always be
> 01 and there should be slashes showing. So no matter how the user
> enters the date in the textbox (whether they use the '/' or not, and/or
> put the day other than 01) it will always override with the correct
> format into the cell. The textbox is always initialized to show
> '01/mm/yyyy' which the user will type over. I have tried a couple of
> ways but can't get it to work properly:
>
> This is the line of code I'm working from, where textbox3 is the place
> the date is entered:
>
> If OptionButton5 Then Cells(Nextrow, 3) = TextBox3.Text
>
> I have tried using:
> TextBox3.Text = format(textBox3.Text, "01/mm/yyyy")
> which just literally adds 01/mm/yyyy into the box after the textbox
> text
>
> and I've tried:
>
> With TextBox3
> Select Case Len(.Text)
> Case 2, 5
> Text = .Text & "/"
> End Select
>
> If InStr(.Text, "//") Then
> Text = Replace(.Text, "//", "/")
> End If
> End With
>
> which doesn't do anything. I have also considered using the calender
> but this isn't suitable on this occasion. I would appreciate help with
> this, thanks!
>
>
> --
> Blondegirl
> ------------------------------------------------------------------------
> Blondegirl's Profile:
http://www.excelforum.com/member.php...o&userid=29615
> View this thread: http://www.excelforum.com/showthread...hreadid=543678
>

Thanks for that Bob, but unfortunately, it's not quite working properly - should a user not put the slashes in, it inserts adhoc numbers into the cell instead of the correct date, and depending on how the user enters the date, it is possible to have the 01 as well as the full dd/mm/yyyy.

Instead, I am now considering a different validation approach in which a message box appears if there are no '/' and also no '01' as the first two characters in the textbox text. Once again, I don't know how to code this and would really appreciate some help. (sorry, I'm a newbie at VBA!)

Something like

With TextBox1
If Not IsDate(.Text) Then
MsgBox "Invalid date"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
ElseIf CDate(.Text) > Date Then
.Text = "01/" & Format(.Text, "mm/yyyy")
End If
End With


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Blondegirl" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for that Bob, but unfortunately, it's not quite working properly
> - should a user not put the slashes in, it inserts adhoc numbers into
> the cell instead of the correct date, and depending on how the user
> enters the date, it is possible to have the 01 as well as the full
> dd/mm/yyyy.
>
> Instead, I am now considering a different validation approach in which
> a message box appears if there are no '/' and also no '01' as the first
> two characters in the textbox text. Once again, I don't know how to
> code this and would really appreciate some help. (sorry, I'm a newbie
> at VBA!)
>
>
> --
> Blondegirl
> ------------------------------------------------------------------------
> Blondegirl's Profile:
http://www.excelforum.com/member.php...o&userid=29615
> View this thread: http://www.excelforum.com/showthread...hreadid=543678
>

Thanks a lot for that Bob, I've got it to work just how I need it now!