Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
condtion for entering date | Setting up and Configuration of Excel | |||
In Excel 2003, entering date without slashes, the date is incorre. | Excel Discussion (Misc queries) | |||
converting date from a textbox to a date format | Excel Programming | |||
Excel 5.0 entering date | Excel Programming | |||
Selecting or Entering a Textbox | Excel Programming |