Prev: Email Last row to an email address in the last active cell
Next: Find and Then Loop
From: Bob on 20 Apr 2010 12:36
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

From: Dave Peterson on 20 Apr 2010 13:19
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


Bob wrote:
>
> 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
From: B Lynn B on 20 Apr 2010 13:29
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.

"Bob" wrote:

> 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
>
From: B Lynn B on 20 Apr 2010 13:49
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.

"B Lynn B" wrote:

> 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.
>
> "Bob" wrote:
>
> > 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
> >
From: Bob on 20 Apr 2010 16:08
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" wrote:

> 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
>
 |  Next  |  Last
Pages: 1 2
Prev: Email Last row to an email address in the last active cell
Next: Find and Then Loop