Manipulate user entered text

[NJ]

Hi. I would like to add a series of filler 'X's after a user enters text. For example, if I wanted a cell length to be 20, and a user entered 'My name is' then the text that would appear in the field would be
'My name isXXXXXXXXXX
'My name isXXXXXXXXXXXXXXXXXXXX' for a cell length of 30, etc..

Thanks!

 

[Frank Kabel]

Hi
only possible with VBA (using an event procedure) Would this be
feasible for you?

 

[NJ]

No, but thanks for the info!

 

[Frank Kabel]

Hi
then try the following macro. Put this in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim text_len As Integer
If Application.Intersect(Target, Range("A1:A10")) Is _
Nothing Then
Exit Sub
End If
text_len = 20
If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo EndMacro
Application.EnableEvents = False
With Target
If .Value <> "" And Len(.Value) < text_len Then
.Value = .Value & Application.WorksheetFunction.Rept("X", _
text_len - Len(.Value))
End If
End With

EndMacro:
Application.EnableEvents = True
End Sub


'-.-------
more about event procedures: http://www.cpearson.com/excel/events.htm

 

[RHerb]

I'm curious as to why you can't create a new custom format by usin
"Format", "Custom" and then enter:

"My Name Is: "@

As a format. Then format the column of cells you will be entering th
name in.

Is it because the cell doesn't adjust to the lenth?

 

[Frank Kabel]

Hi
this won't give you the length. It will just display the entered text
with your prefix