Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use only numbers and '/'
I have a textbox in a userform that I want to only accept numbers and '/' to
enter dates. How do I do that? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use only numbers and '/'
Here are a couple of routines that I use that you can play about with, they
should be clear private Sub TextBox2_KeyPress(ByVal KeyAscii As _ MSForms.ReturnInteger) Dim cDelim As Long cDelim = Len(TextBox2.Text) - Len(Replace (TextBox2.Text, "/", "")) Select Case KeyAscii Case Asc("0") To Asc("9"): 'OK Case Asc("/"): If cDelim = 2 Then KeyAscii = 0 Else cDelim = cDelim + 1 End If Case Else: KeyAscii = 0 End Select End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 45 'negative If Len(Trim(TextBox1.Text)) 1 Then Beep KeyAscii = 0 End If Case 46 'period If InStr(TextBox1.Text, ".") 0 Then Beep KeyAscii = 0 End If Case 48 To 57 'numbers If InStr(TextBox1.Text, ".") 0 Then If Len(TextBox1.Text) InStr(TextBox1.Text, ".") + 1 Then Beep KeyAscii = 0 End If End If Case Else 'Discard anything else Beep KeyAscii = 0 End Select End Sub -- __________________________________ HTH Bob "ranswrt" wrote in message ... I have a textbox in a userform that I want to only accept numbers and '/' to enter dates. How do I do that? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use only numbers and '/'
Have a look at the keypress event for that textbox.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
use only numbers and '/'
Maybe this will do it
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim B B = TextBox1.Value If B < Format(TextBox1.Value, "mm/dd/yy") Then Cancel = True And MsgBox("Format Must Be mm/dd/yy", vbCritical) End If End Sub "ranswrt" wrote: I have a textbox in a userform that I want to only accept numbers and '/' to enter dates. How do I do that? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
use only numbers and '/'
I have a textbox in a userform that I want to only accept numbers
and '/' to enter dates. How do I do that? You can use the code at the bottom of my posting to do what you asked (it will also stop the user from pasting in text containing characters other than digits and the slash character). You will still have to validate the entry after the user has finished typing his/her entry to make sure it is a valid date (you can use the IsDate function for this). Just out of curiosity, have you considered using one of the drop-down calendar controls to take your date input (the input mechanism is much more natural for the user and it foolproof date-entry-wise)? I use the "Microsoft Date and Time Picker Control" for this purpose. You can add it to your UserForm's Toolbox by right-clicking the Toolbox and clicking on "Additional Controls", then put a checkmark next to the "Microsoft Date and Time Picker Control" item and click OK. Try it... I think you will like it. Rick '***********BEGIN PASTE*********** Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean With TextBox1 If Not SecondTime Then If .Text Like "*[!0-9/]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End If End With SecondTime = False End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) LastPosition = TextBox1.SelStart End Sub '***********END PASTE*********** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
use only numbers and '/'
Thanks for the info for a calender I have wanting to use one of those.
"Rick Rothstein (MVP - VB)" wrote: I have a textbox in a userform that I want to only accept numbers and '/' to enter dates. How do I do that? You can use the code at the bottom of my posting to do what you asked (it will also stop the user from pasting in text containing characters other than digits and the slash character). You will still have to validate the entry after the user has finished typing his/her entry to make sure it is a valid date (you can use the IsDate function for this). Just out of curiosity, have you considered using one of the drop-down calendar controls to take your date input (the input mechanism is much more natural for the user and it foolproof date-entry-wise)? I use the "Microsoft Date and Time Picker Control" for this purpose. You can add it to your UserForm's Toolbox by right-clicking the Toolbox and clicking on "Additional Controls", then put a checkmark next to the "Microsoft Date and Time Picker Control" item and click OK. Try it... I think you will like it. Rick '***********BEGIN PASTE*********** Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean With TextBox1 If Not SecondTime Then If .Text Like "*[!0-9/]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End If End With SecondTime = False End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) LastPosition = TextBox1.SelStart End Sub '***********END PASTE*********** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel, change column of negative numbers to positive numbers? | New Users to Excel | |||
Need to reconcile numbers accounting Harlan Grove code doesn't work for negative numbers | Excel Programming | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions |