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


"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 17:08
Please ignore my previous post. Pilot error.

Regards,
Bob


"B Lynn B" wrote:

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