Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
Hi,
How can i get excel to show requirement for validation? For example I want people to type in a text max 15 characters, but I want them to be able to see how many characters they can type. For example the cell can show XXXXXXXXXXXXXXX and as they start filling it in they swap the X for their own words. I know I can do validation max 15 character length but then they get a error box and need to keep taking away characters untill they get below 15. Its better if they can see it straight away, how many to fill inn. -- Regards Anders |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
Hi,
Are you dealing with input in cells or in text boxes in a user form ...? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
Input in cells.
I have a chart of accounts that has a long text of 40 characters. I need to reduce that to a shorter text of only 15 characters. But since I need it to make sense I have to manually go through them all and reduce the characters. But it would be easier if had a column of 15 XXXXXXXXXXXXXXX that disappeared as I typed in the shortened version of the text. -- Regards Anders "Carim" wrote: Hi, Are you dealing with input in cells or in text boxes in a user form ...? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
I don't know that it's possible to do exactly what you ask. However, have
you seen the "Input Message" and "Error Alert" tabs on the Data Validation pop-up window? If you enter an Input Message (for example, "Max 15 characters"), this will pop up next to the cell whenever it is selected. Alternatively (or additionally), an Error Alert will pop up if the data entry does not conform with the validation. "Anders" wrote in message ... Hi, How can i get excel to show requirement for validation? For example I want people to type in a text max 15 characters, but I want them to be able to see how many characters they can type. For example the cell can show XXXXXXXXXXXXXXX and as they start filling it in they swap the X for their own words. I know I can do validation max 15 character length but then they get a error box and need to keep taking away characters untill they get below 15. Its better if they can see it straight away, how many to fill inn. -- Regards Anders |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
Yep, know that I can do that. But that just gets annoying, cause then I have
to reduce one and one character untill I get it right. For now the best way seems to be to put 15 X in a column and then using "insert" but I find that type to many and or forget to pres F2 and home.... And need to count the characters anyways. Hope there is a solution, I know that I have seen the function before but maybe it was in Access? -- Regards Anders "Stephen" wrote: I don't know that it's possible to do exactly what you ask. However, have you seen the "Input Message" and "Error Alert" tabs on the Data Validation pop-up window? If you enter an Input Message (for example, "Max 15 characters"), this will pop up next to the cell whenever it is selected. Alternatively (or additionally), an Error Alert will pop up if the data entry does not conform with the validation. "Anders" wrote in message ... Hi, How can i get excel to show requirement for validation? For example I want people to type in a text max 15 characters, but I want them to be able to see how many characters they can type. For example the cell can show XXXXXXXXXXXXXXX and as they start filling it in they swap the X for their own words. I know I can do validation max 15 character length but then they get a error box and need to keep taking away characters untill they get below 15. Its better if they can see it straight away, how many to fill inn. -- Regards Anders |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
Hi,
To monitor 15 Alphabetic characters : Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo OuttaHere If Target.Address = "$C$10" Then '<<<< change cell to suit needs Dim strText As String Dim lngN As Long Const str_Chars As String = "[a-zA-Z ]" Application.EnableEvents = False strText = Target.Text If Len(strText) <= 15 Then For lngN = 1 To Len(strText) If Not Mid$(strText, lngN, 1) Like str_Chars Then MsgBox "Only Alphabetic characters allowed. ", vbOKOnly, " Blame Anders" Application.Undo Exit For End If Next Else MsgBox "Only 15 Alphabetic characters allowed. ", vbOKOnly, " Blame Anders" Application.Undo End If End If OuttaHe Application.EnableEvents = True End Sub HTH |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
Here's an example of how this is possible. In B1, I have this
formula: =LEN(A1)<=15 In A1, I have this custom validation rule: =B1 If the input is more than 15 characters, user will get an error message. However, you must have calculations on automatic for this to function. On Dec 19, 3:47 am, Anders wrote: Hi, How can i get excel to show requirement for validation? For example I want people to type in a text max 15 characters, but I want them to be able to see how many characters they can type. For example the cell can show XXXXXXXXXXXXXXX and as they start filling it in they swap the X for their own words. I know I can do validation max 15 character length but then they get a error box and need to keep taking away characters untill they get below 15. Its better if they can see it straight away, how many to fill inn. -- Regards Anders |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Format
Modify this like so to meet your alphabetic characters only criteria.
In cell C1, use this formula (array entered via Ctrl+Shift+Enter): =LEN(A1)-SUM(--((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))), 1))=CODE("A"))*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN (A1))), 1))<=CODE("Z"))+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN (A1))), 1))=CODE("a"))*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN (A1))), 1))<=CODE("z")))) This formula will return number of characters in the string that are not alphabetic. Modify the validation rule in A1 to read as follows: =B1*(C1=0) Now user will receive the error message under two conditions: 1. More than 15 characters entered 2. Any character in string that is not A-Z or a-z You will want to hide these columns, particularly the one that checks non-alpha characters, because it returns an error if the input cell is empty (#REF). On Dec 19, 9:25 am, iliace wrote: Here's an example of how this is possible. In B1, I have this formula: =LEN(A1)<=15 In A1, I have this custom validation rule: =B1 If the input is more than 15 characters, user will get an error message. However, you must have calculations on automatic for this to function. On Dec 19, 3:47 am, Anders wrote: Hi, How can i get excel to show requirement for validation? For example I want people to type in a text max 15 characters, but I want them to be able to see how many characters they can type. For example the cell can show XXXXXXXXXXXXXXX and as they start filling it in they swap the X for their own words. I know I can do validation max 15 character length but then they get a error box and need to keep taking away characters untill they get below 15. Its better if they can see it straight away, how many to fill inn. -- Regards Anders |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
Convert Excel 2007 format (*.xlsx) into Excel 2003 format | Excel Discussion (Misc queries) | |||
excel numbers in general format i cant add cant change format | Excel Worksheet Functions | |||
How to covert picture excel format to editable excel format | Excel Discussion (Misc queries) | |||
how to format excel format to text format with separator "|" in s. | New Users to Excel |