Formatting a date from a textbox
 

Hello. I'm trying to format the date entry in a textbox on a form s
that when a future date is entered via the form onto the relevant cel
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 b
01 and there should be slashes showing. So no matter how the use
enters the date in the textbox (whether they use the '/' or not, and/o
put the day other than 01) it will always override with the correc
format into the cell. The textbox is always initialized to sho
'01/mm/yyyy' which the user will type over. I have tried a couple o
ways but can't get it to work properly:

This is the line of code I'm working from, where textbox3 is the plac
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 textbo
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 calende
but this isn't suitable on this occasion. I would appreciate help wit
this, thanks

--
Blondegir
-----------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...fo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=54367

Formatting a date from a textbox
 

Try

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Blondegirl" wrote
in message ...

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

Formatting a date from a textbox
 

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

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

--
Blondegir
-----------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...fo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=54367

Formatting a date from a textbox
 

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" wrote
in message ...

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

Formatting a date from a textbox
 

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

--
Blondegir
-----------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...fo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=54367