Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation
Help for a form newbie.
I need to verify input to a text box in a form. It must be exactly 6 characters. First is always "p" The next 5 are any number. I know how to verify if it is all a number but not sure how to do it for a combo of letter and numbers. Any help would be appreciated. Sorry if this is a second post. I posted yesterday but did not see it appear. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation
Dave,
You could use the change event of the textbox. The example below if for Testbox1. You would also need to check the length of the Textbox1.Text prior to accepting further processing, say when the OK button is clicked. Private Sub CommandButton1_Click() If Len(TextBox1.Text) < 6 Then Exit Sub 'Other code here End Sub HTH, Bernie MS Excel MVP Private Sub TextBox1_Change() Dim mytest As Double If Len(TextBox1.Text) = 0 Then Exit Sub If Len(TextBox1.Text) = 1 Then If UCase(TextBox1.Text) = "P" Then Exit Sub Else MsgBox "You have to start with a ""P""" TextBox1.Text = "" Exit Sub End If End If If Len(TextBox1.Text) 6 Then TextBox1.Text = Left(TextBox1.Text, 6) MsgBox "That's long enough!" Exit Sub End If On Error GoTo Bad mytest = CDbl(Mid(TextBox1.Text, 2, 6)) Exit Sub Bad: MsgBox "You need to enter a P followed by 5 digits!" TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1) End Sub "Dave" wrote in message ... Help for a form newbie. I need to verify input to a text box in a form. It must be exactly 6 characters. First is always "p" The next 5 are any number. I know how to verify if it is all a number but not sure how to do it for a combo of letter and numbers. Any help would be appreciated. Sorry if this is a second post. I posted yesterday but did not see it appear. Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation
Thanks very much Bernie, Works great!
-----Original Message----- Dave, You could use the change event of the textbox. The example below if for Testbox1. You would also need to check the length of the Textbox1.Text prior to accepting further processing, say when the OK button is clicked. Private Sub CommandButton1_Click() If Len(TextBox1.Text) < 6 Then Exit Sub 'Other code here End Sub HTH, Bernie MS Excel MVP Private Sub TextBox1_Change() Dim mytest As Double If Len(TextBox1.Text) = 0 Then Exit Sub If Len(TextBox1.Text) = 1 Then If UCase(TextBox1.Text) = "P" Then Exit Sub Else MsgBox "You have to start with a ""P""" TextBox1.Text = "" Exit Sub End If End If If Len(TextBox1.Text) 6 Then TextBox1.Text = Left(TextBox1.Text, 6) MsgBox "That's long enough!" Exit Sub End If On Error GoTo Bad mytest = CDbl(Mid(TextBox1.Text, 2, 6)) Exit Sub Bad: MsgBox "You need to enter a P followed by 5 digits!" TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1) End Sub "Dave" wrote in message ... Help for a form newbie. I need to verify input to a text box in a form. It must be exactly 6 characters. First is always "p" The next 5 are any number. I know how to verify if it is all a number but not sure how to do it for a combo of letter and numbers. Any help would be appreciated. Sorry if this is a second post. I posted yesterday but did not see it appear. Thanks in advance . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming |