Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form questions
Thanks to a few in the general forum, I have successfully used
Data-Validation. I have since then decided that the best way to have data entered is by using a form as there are thousands of records. My questions a 1. How can I enforce a unique entry into a textbox? 2. How can I enforce a date being entered (in mm/dd/yy format with the "/"). 3. How can I enforce a phone number being entered ie 10 digits with no dashes. I have another entry for a serial number which would be x amount of characters, but I think that would be the same as the phone number scenario. Thanks in advance, Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form questions
Hi Richard
1 You can not enforce an entry. Annoy the user long enough and she'll turn off the computer without completing the form. Your best hope is to inform that the entry already exists in a helpful way. 2 Same as 1, but also why the h*? A user should imo be allowed to enter things like June 1 2006 into a date field and expect the computer to get it. Entries should make sense to the /user/. Your job as a developer is to translate anything of that kind into the stuff that your program needs. 3 same as 1 but also 2. (Now if these three replies were a series of responses to your work from my computer program, would you love using it ? ;-) HTH. Best wishes Harald "Richard" skrev i melding ... Thanks to a few in the general forum, I have successfully used Data-Validation. I have since then decided that the best way to have data entered is by using a form as there are thousands of records. My questions a 1. How can I enforce a unique entry into a textbox? 2. How can I enforce a date being entered (in mm/dd/yy format with the "/"). 3. How can I enforce a phone number being entered ie 10 digits with no dashes. I have another entry for a serial number which would be x amount of characters, but I think that would be the same as the phone number scenario. Thanks in advance, Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form questions
1. Check the value against your saved values using. Assuming it is on a
worksheet , use the Application.MATCH worksheetfunction. Dim iMatch As Long On Error Resume Next iMatch = Application.MATCH(Textbox1.Text,Worksheets("Sheet1 ").Range("A1:A100"),0) On Error Goto 0 If iMatch < 0 Then 'not unique End If 2. After the data has been completed, check the textbox text value with the IsDate function. I would do this on a commit button, and it fails you reset back to the textbox With TextBox1 If Not IsDate(.Text) Then 'some sort of error message .SelLength = Len(.Text) .SelStart = 0 .SetFocus End If End With 3. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = 45 Then '- KeyAscii = 0 End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Richard" wrote in message ... Thanks to a few in the general forum, I have successfully used Data-Validation. I have since then decided that the best way to have data entered is by using a form as there are thousands of records. My questions a 1. How can I enforce a unique entry into a textbox? 2. How can I enforce a date being entered (in mm/dd/yy format with the "/"). 3. How can I enforce a phone number being entered ie 10 digits with no dashes. I have another entry for a serial number which would be x amount of characters, but I think that would be the same as the phone number scenario. Thanks in advance, Richard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form questions
Of course 2 only checks a valid date, it doesn't force an input in a
prescribed format. This can get very complex and is probably not worth the effort. -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... 1. Check the value against your saved values using. Assuming it is on a worksheet , use the Application.MATCH worksheetfunction. Dim iMatch As Long On Error Resume Next iMatch = Application.MATCH(Textbox1.Text,Worksheets("Sheet1 ").Range("A1:A100"),0) On Error Goto 0 If iMatch < 0 Then 'not unique End If 2. After the data has been completed, check the textbox text value with the IsDate function. I would do this on a commit button, and it fails you reset back to the textbox With TextBox1 If Not IsDate(.Text) Then 'some sort of error message .SelLength = Len(.Text) .SelStart = 0 .SetFocus End If End With 3. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = 45 Then '- KeyAscii = 0 End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Richard" wrote in message ... Thanks to a few in the general forum, I have successfully used Data-Validation. I have since then decided that the best way to have data entered is by using a form as there are thousands of records. My questions a 1. How can I enforce a unique entry into a textbox? 2. How can I enforce a date being entered (in mm/dd/yy format with the "/"). 3. How can I enforce a phone number being entered ie 10 digits with no dashes. I have another entry for a serial number which would be x amount of characters, but I think that would be the same as the phone number scenario. Thanks in advance, Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
two questions, Yes/No form control, and how did they create this adjustable area on the sheet... | Excel Discussion (Misc queries) | |||
two questions, Yes/No form control, and how did they create this adjustable area on the sheet... | Excel Worksheet Functions | |||
Two Questions....Order Form | Excel Discussion (Misc queries) | |||
Further Form Questions | Excel Programming | |||
Further simple Form questions | Excel Programming |