Can you copy an Excel cell without the automatic line break?

  • Thread starter Thread starter chillynois
  • Start date Start date
C

chillynois

Whenever you copy a cell (CTRL+C), Excel automatically appends a line
break at the end. This makes it impossible to paste the cell directly
into a single-line text field.

Can you copy an Excel cell without the automatic line break?

Is there a hidden line break in the cell that I can remove?

Is there way I can get Excel to place the result directly in the
clipboard without the automatic line break?

Is there a way I can get VB to interface with Excel so that I can get a
copyable field that doesn't contain an automatic line break?
 
I don't know if you can stop the way excel works, but you could have your own
macro that does what you want.

Option Explicit
Sub testme()

Dim MyDataObj As New DataObject
Dim myStr As String

myStr = ActiveCell.Text
If Right(myStr, 1) = vbLf Then
myStr = Left(myStr, Len(myStr) - 1)
End If

MyDataObj.SetText myStr
MyDataObj.PutInClipboard

End Sub

You will need a reference to microsoft forms 2.0 object library.

Chip Pearson has some nice notes at:
http://www.cpearson.com/excel/clipboar.htm
for working with the windows clipboard.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

===
and one more question.

If you paste and hit the backspace key, does that work (without all this code)?
 
If you paste and hit the backspace key, does that work (without all this code)?

When I try to paste multi-line text in the single-line field, the
single-line field becomes empty. There is a code freeze on the
application that contains this single-line field, so I can't make the
single-line field be more user friendly.

Thank you for your response. I will try it out tomorrow at work.
 
Dave said:
I don't know if you can stop the way excel works, but you could have your own
macro that does what you want.

Option Explicit
Sub testme()
Click to expand...

Thank you for the code. I placed it into a Worksheet_SelectionChange
event so that it would copy text into the clipboard whenever the user
clicks the cell labeled "Copy results to Clipboard".
 
Dave said:
I don't know if you can stop the way excel works, but you could have your own
macro that does what you want.

Option Explicit
Sub testme()
Click to expand...

Thank you for the code. I placed it into a Worksheet_SelectionChange
event so that it would copy text into the clipboard whenever the user
clicks the cell labeled "Copy results to Clipboard".

Below is the code that I used. "$A$6:$C$6" are the merged cells that
form the "Copy button", and "$A$5" is the cell containing the results
to copy.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyDataObj As New DataObject
Dim myStr As String

If Target.Address = "$A$6:$C$6" Then
myStr = Range("$A$5").Text
MyDataObj.SetText myStr
MyDataObj.PutInClipboard
End If
End Sub
 
Back
Top