Solved: Texbox - Numbers ONLY - Date

[Philcjr]

This is a two prong question:

1) How can I ONLY allow a user to enter in numbers in a textbox?
I want to restrict the keystrokes at the keyboard... that is to say, if the press any letters or characters they do not populate the texbox, but if they press numbers they are entered into the textbox.


2) Is there a way to format a textbox with a "mask"?
Example: MM/DD/YY

I would like the user to type in a date - two digit month, two digit day and two digit year... only

Once this is done, I will have to test to ensure that it is truly a date.

Thoughts?

Phil

[Zack Barresse]

Hey Phil,

How are ya? Hope all is well. Long time, no hear.

Anyway, your answers. Their dependent on whether or not you are referring to a VBA solution or a worksheet function solution. I'm going to assume with all our past dealings that you're talking VBA solution, probably on a UserForm. Yes?

1) Perform an IsNumeric test on it. Something like this ...

[vba]If IsNumeric(Me.TextBox1.Value) Then
'code for true
Else
'code for false
End If[/vba]

As far as your number 2 goes, that gets a little more difficult. You can perform a check on it by using the IsDate function in VBA (which this is inaccessible as a worksheet function, hence my KB entry here). Truthfully, it won't matter what format they use, barring your system regional settings, as long as it's a recognized date.

I know we've talked about this date issue before. You've mentioned quite a few times about textboxes, I think I've shot back 'use comboboxes'. I still suggest using comboboxes. You can then piece-meal your date together with DateSerial function to a Date variable. I've seen a LOT of users and IMHO this is the easiest way to go and most universally recognized. (Yes, I'm going to keep preaching the combobox issue to you. LOL!)

Take care buddy.

[Philcjr]

Zack,

True enough we have spoken about different methods of gathering date inputs. I pose this question because of something I read in a Visual Basic .Net book. It goes something like this... "If e.Keychar < "0" OrElse e.KeyChar > "9" then e.Handled = true" this "if" test is triggers when a KeyPress is detected in the Textbox

Not knowing all the ins and out of VBA, I was hoping something like this would apply here.

* Yes, this would be on a user-form
* I like the combo-boxes and will be using them... in fact almost done loading them into the file
* Late night reading keeps my mind firing different ideas

[Bob Phillips]

Trap the keypress

[VBA]
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9"): 'OK
Case Else: KeyAscii = 0
End Select
End Sub
[/VBA]

as a starter example

.

[Zack Barresse]

The keypress will work. I think I have it in a few of my older workbooks. Xld gives you an example even. But I am still partial to the comboboxes.

[Philcjr]

XLD,

Thanks for your input, huge help.

All,

I am attaching my version of a TextBox that will capture a date that a user inputs.

ANY suggestions by anyone would greatly be appreciated.

Oh ya, here is the code incase you dont want to download the file...
[VBA]
Option Explicit

Private Sub CommandButton1_Click()
If IsDate(TextBox1.Value) = False Or Left(TextBox1.Value, 2) > 12 Or _
Mid(TextBox1.Value, 4, 2) > 31 Then MsgBox ("This is not a date, Please try again") _
Else MsgBox ("This is a date, Good for you")
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim Temp As String
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Else: KeyAscii = 0
End Select

If Len(TextBox1) = 2 Or Len(TextBox1) = 5 Then _
TextBox1.Value = TextBox1.Value & "/"

End Sub

Private Sub UserForm_Initialize()
TextBox1.Value = Format(Date, "MM/DD/YY")
CommandButton1.SetFocus
End Sub

[/VBA]

[Bob Phillips]

[VBA]Private Sub CommandButton1_Click()
If IsDate(TextBox1.Value) = False Or Left(TextBox1.Value, 2) > 12 Or _
Mid(TextBox1.Value, 4, 2) > 31 Then MsgBox ("This is not a date, Please try again") _
Else MsgBox ("This is a date, Good for you")
End Sub
[/VBA]

This is overkill as the IsDate function will not allow an invalid number of days or months, so all you need is

[VBA]
Private Sub CommandButton1_Click()
If Not IsDate(TextBox1.Value) Then
MsgBox ("This is not a date, Please try again")
Else
MsgBox ("This is a date, Good for you")
End If

End Sub
[/VBA]

In addition, your way only works for US dates, as we continentals use a dd/mm/yy format, where the first two digits can be greater than 12. Mine works for any.

.

[Philcjr]

XLD,

Thanks for the heads-up... However, if I wanted to ensure that a US date only was entered correctly, would my way surfice?

The rare times I get to create files that capture date(s) would only be that of US Dates. I will, nevertheless, be mindful of US dates and Continental dates.

Thanks for all your input/help

[Bob Phillips]

However, if I wanted to ensure that a US date only was entered correctly, would my way surfice?

Your way works for US only dates if it will only be used in the US, but over here it fails because it will not let me input a day of 13, and if I revert the format, the IsDate fails.

My suggestion works for US dates in the US, continental dates in the continent.

.

[Philcjr]

Thanks, XLD

[Zack Barresse]

The thing you have to remember about Dates, Phil, is that they are all basically Serial Numbers with different formatting applied. The broader you use them, the easier it is; generally and usually. Being broad with the IsDate() function should suffice you and make it less stressful to use anything else. I mean in all honesty, it really doesn't matter what format it's shown in, it all pans out the same.

[Bob Phillips]

I mean in all honesty, it really doesn't matter what format it's shown in, it all pans out the same.

Not in VBA. There US dates rule

[Zack Barresse]

Sorry, meant that for specific regional settings & using the IsDate function.