Formatting a date from a textbox

[Blondegirl]

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

 

[Bob Phillips]

Try

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Blondegirl]

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!

 

[Bob Phillips]

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]

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