Attaching a function

[Guest]

I have an excel worksheet. A user enters data in a cell. But he can enter the
data with leading and traliling spaces. So after the user enters data in the
cell, I want to attach like TRIM function to remove the spaces in the same
cell. Can some one help me how to do this?
Thank you

 

[Gord Dibben]

In the same cell?

You would need event code to do that as the data is entered.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False

If (Not IsEmpty(Target)) And _
Not IsNumeric(Target.Value) And _
InStr(Target.Formula, "=") = 0 _
Then Target.Value = Application.Trim(Target.Value)
End If
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[JE McGimpsey]

To do so would require an event macro. Put this in your worksheet code
module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
On Error GoTo ErrorOrExit
Application.EnableEvents = False
For Each rArea In Target
For Each rCell In rArea
With rCell
If Not .HasFormula Then _
If Not IsNumeric(.Value) Then _
.Value = Trim(.Value)
End With
Next rCell
Next rArea
ErrorOrExit:
Application.EnableEvents = True
End Sub



But one can usually work around the problem by using TRIM() in any
formulas that reference the cell.