Need help with Trim function

[Bob]

The following block of code strips away any formatting from copied text
before it is pasted.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim Temp As Variant
Temp = Target.Formula
On Error GoTo endit
With Application
.EnableEvents = False
.Undo
Target.Formula = Temp
.EnableEvents = True
End With
endit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I would like to ensure that any extra spaces in the copied text is also
removed before the text is pasted.

Can someone show me how to incorporate the Trim function in the code above.

Thanks,
Bob

 

[Dave Peterson]

If you were always copy|pasting a single cell, you could use something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim Temp As Variant

if target.cells.count > 1 then
exit sub 'one cell at a time???
end if

Temp = Target.Formula
On Error GoTo endit
With Application
.EnableEvents = False
.Undo
Target.Formula = application.trim(Temp)
.EnableEvents = True
End With
endit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

But if you were pasting multiple cells and some were plain text and some were
formulas, you may want something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim TempAddr() As String
Dim TempFormula() As String
Dim IsItAFormula() As Boolean
Dim TempFormat() As String

Dim myCell As Range
Dim cCtr As Long

ReDim TempFormula(1 To Target.Cells.Count)
ReDim TempAddr(1 To Target.Cells.Count)
ReDim IsItAFormula(1 To Target.Cells.Count)
ReDim TempFormat(1 To Target.Cells.Count)

cCtr = 0
For Each myCell In Target.Cells
cCtr = cCtr + 1
TempAddr(cCtr) = myCell.Address(0, 0)
TempFormula(cCtr) = myCell.Formula
TempFormat(cCtr) = myCell.NumberFormat
If myCell.HasFormula Then
IsItAFormula(cCtr) = True
Else
IsItAFormula(cCtr) = False
End If
Next myCell

On Error GoTo endit

With Application
.ScreenUpdating = False
.EnableEvents = False
.Undo
For cCtr = 1 To UBound(TempFormula)
With Me.Range(TempAddr(cCtr))
.NumberFormat = TempFormat(cCtr)
If IsItAFormula(cCtr) Then
.Formula = "=Trim(" & Mid(TempFormula(cCtr), 2) & ")"
Else
.Formula = TempFormula(cCtr)
End If
End With
Next cCtr
End With

endit:
On Error Resume Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[B Lynn B]

replace line Temp = Target.Formula with Temp = Trim(Target.Formula)

Might be worth noting that this will not leave any actually formulas on your
sheet should you try to put them in. If I were doing this, I might be
inclined to put a test on the cell contents to see if the first character is
"=", then skip the action if true.

 

[B Lynn B]

I realized I should also mention that it might be a good idea to account for
the possibility of your change event involving more than one cell, which
would cause your procedure to error out. If a multi-cell range were pasted,
either your line or mine would not be executable.

 

[Bob]

B Lynn B and Dave,

Thanks for your help. I really appreciate it.

In answer to your two questions, only one cell at a time will ever be
pasted, and no formulas will ever be pasted. Only formatted text (usually
from a website).

Regards,
Bob

 

[Bob]

After I incorporated your change, I discovered that it unfortunately did not
work. As a test, I pasted some unformatted text with extra spaces at the
end. Once pasted in my workbook, I discovered that the extra spaces still
existed.

Regards,
Bob

 

[Bob]

Please ignore my previous post. Pilot error.

Regards,
Bob