Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Canned VBA Date Validation
I want to validate a date entered into a textbox on a
form, before committing it to the target cell. This is because the cell date format doesn't seem to append the year if a date like 1/1 is entered (for some reason even though the target cell is formatted as a date, when the textbox value is copied into the cell, the format doesn't change the entry, as it would when manually entered). If either the cell formatting or some easy VB validation script can do this, it would be ideal. The goal is to have the entry in dd/mm/ccyy format for consistency. I could write something to parse the input and either append the year or return the date to the user for correction, but I was hoping there is something canned out there so I don't re-invent the wheel. thanks Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Canned VBA Date Validation
Hi Kevin,
You can use the IsDate function to see if a text string "looks like" a date to VBA. Then you can use Format$() to format the string in the way you'd like. Here's a simple example: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox1 If IsDate(.Text) Then '/ format it .Text = Format$(.Text, "mm/dd/yyyy") Else '/ error MsgBox "The value you entered is not a date." .SelStart = 0 .SelLength = Len(.Text) Cancel = True End If End With End Sub -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Kevin wrote: I want to validate a date entered into a textbox on a form, before committing it to the target cell. This is because the cell date format doesn't seem to append the year if a date like 1/1 is entered (for some reason even though the target cell is formatted as a date, when the textbox value is copied into the cell, the format doesn't change the entry, as it would when manually entered). If either the cell formatting or some easy VB validation script can do this, it would be ideal. The goal is to have the entry in dd/mm/ccyy format for consistency. I could write something to parse the input and either append the year or return the date to the user for correction, but I was hoping there is something canned out there so I don't re-invent the wheel. thanks Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Canned VBA Date Validation
for some reason even though the target cell is formatted as a date, when
the textbox value is copied into the cell, the format doesn't change the entry, as it would when manually entered). You should do a Range("XX").Value = Textbox.Value rather than a copy/paste -- Jim Rech Excel MVP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Canned VBA Date Validation
If available, it is much more pleasant to use a dedicated control. For
example, a quick look on my machine reveals the Microsoft Date and Time Picker Control 6.0 (MSDATGRD.OCX). Before using in a solution, ensure any controls you want to use are available on the client machine or that you are able to distribute them. -- "Kevin" wrote in message ... I want to validate a date entered into a textbox on a form, before committing it to the target cell. This is because the cell date format doesn't seem to append the year if a date like 1/1 is entered (for some reason even though the target cell is formatted as a date, when the textbox value is copied into the cell, the format doesn't change the entry, as it would when manually entered). If either the cell formatting or some easy VB validation script can do this, it would be ideal. The goal is to have the entry in dd/mm/ccyy format for consistency. I could write something to parse the input and either append the year or return the date to the user for correction, but I was hoping there is something canned out there so I don't re-invent the wheel. thanks Kevin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Canned VBA Date Validation
If not isdate(TextBox1.Text) then exit sub
"Kevin" wrote in message ... I want to validate a date entered into a textbox on a form, before committing it to the target cell. This is because the cell date format doesn't seem to append the year if a date like 1/1 is entered (for some reason even though the target cell is formatted as a date, when the textbox value is copied into the cell, the format doesn't change the entry, as it would when manually entered). If either the cell formatting or some easy VB validation script can do this, it would be ideal. The goal is to have the entry in dd/mm/ccyy format for consistency. I could write something to parse the input and either append the year or return the date to the user for correction, but I was hoping there is something canned out there so I don't re-invent the wheel. thanks Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATE VALIDATION | Excel Discussion (Misc queries) | |||
Is there a way to use a cell value as a parm in a canned function? | Excel Discussion (Misc queries) | |||
Date Validation - Must equal Sundays date | Excel Discussion (Misc queries) | |||
Add canned comment | Excel Discussion (Misc queries) | |||
PivotTable canned functions | Excel Discussion (Misc queries) |