Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Trim function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Trim function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Trim function
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Trim function
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Trim function
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Trim function
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Trim function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function =Trim() | Excel Worksheet Functions | |||
Trim Function | Excel Programming | |||
TRIM function | Excel Worksheet Functions | |||
Trim Function | Excel Discussion (Misc queries) | |||
Trim Function | Excel Worksheet Functions |