Entering a date in a textbox
 

Hi,

I have a textbox which is linked to a cell on my spreadsheet. Lets say
cell A1 is "25/04/2005". When I open my form, the textbox will display
"25/04/2005" and the cursor will be sat to the right of the date.

I would like the cursor to start at the left so I don't have to key
back to the first number and then I would like my numbers to be
separated with slashes / .

At the moment the cursor is on the right, so I have to key back accross
and if i type 26052006 it just displays 2605200625/04/2005. When i start
to type the new date I would like my numbers to just override the
current numbers but also retain the "##/##/####" format without me
having to type in the /'s.

Please can anyone help??


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=389730

Entering a date in a textbox
 

Hi,

Firstly, to select all of the text in your textbox at start up, place
the following code in your userform's initialize procedure (modify as
necessary:

Private Sub UserForm_Initialize()
'copy from here
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
'end copy here
End Sub

Then, to effect some type of mask you could use either:

Private Sub TextBox1_Change()
Select Case Len(TextBox1.Text)
Case 2, 5
TextBox1.Text = TextBox1.Text & "/"
End Select
End Sub

which just inserts the slashes as you type. Alternatively, you could do
something like:

Private Sub TextBox1_Change()
Dim iPos As Integer
Const myMask As String = "--/--/----"

If Len(TextBox1.Text) = 1 Then
TextBox1.Text = TextBox1.Text & myMask
Exit Sub
End If

iPos = InStr(TextBox1.Text, "-")
If iPos = 0 Then Exit Sub
TextBox1.Text = Left$(TextBox1.Text, iPos - 1) _
& Mid(myMask, iPos)

iPos = InStr(TextBox1.Text, "-")
If iPos = 0 Then Exit Sub
TextBox1.SelStart = InStr(TextBox1.Text, "-") - 1

End Sub

which crudely writes a mask into the textbox.

HTH,

Gareth


johncassell wrote:
Hi,

I have a textbox which is linked to a cell on my spreadsheet. Lets say
cell A1 is "25/04/2005". When I open my form, the textbox will display
"25/04/2005" and the cursor will be sat to the right of the date.

I would like the cursor to start at the left so I don't have to key
back to the first number and then I would like my numbers to be
separated with slashes / .

At the moment the cursor is on the right, so I have to key back accross
and if i type 26052006 it just displays 2605200625/04/2005. When i start
to type the new date I would like my numbers to just override the
current numbers but also retain the "##/##/####" format without me
having to type in the /'s.

Please can anyone help??

Entering a date in a textbox
 

Thanks very much for that Gareth, it worked exactly as I'd hoped an
many thanks for replying in the first place

--
johncassel
-----------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...fo&userid=2501
View this thread: http://www.excelforum.com/showthread.php?threadid=38973

Entering a date in a textbox
 

You're welcome - I'm glad it works for you.

I thought about this again and realised if you have a user that doesn't
know they can omit the /'s they will get double slashes. So I added a
line of code in each routine to avoid this:

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

It works in both - see updated procedures below.

HTH,
Gareth

Private Sub TextBox1_Change()

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

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

End Sub

Private Sub TextBox2_Change()
Dim iPos As Integer
Const myMask As String = "--/--/----"

With TextBox2

If Len(.Text) = 1 Then
.Text = .Text & myMask
Exit Sub
End If

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

iPos = InStr(.Text, "-")
If iPos = 0 Then Exit Sub
.Text = Left$(.Text, iPos - 1) _
& Mid(myMask, iPos)

iPos = InStr(.Text, "-")
If iPos = 0 Then Exit Sub
.SelStart = InStr(.Text, "-") - 1

End With

End Sub


johncassell wrote:
Thanks very much for that Gareth, it worked exactly as I'd hoped and
many thanks for replying in the first place.