Excel Format

[Anders]

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.

 

[Carim]

Hi,

Are you dealing with input in cells or in text boxes in a user
form ...?

 

[Anders]

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.

 

[Stephen]

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]

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?

 

[Sandy Mann]

You could use a Worksheet_Change event Macro to cut the entry down to the
first 15 characters

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Carim]

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
OuttaHere:
Application.EnableEvents = True
End Sub

HTH

 

[iliace]

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.

 

[iliace]

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).

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.

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.