+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1

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

    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?


  2. #2
    Dave Peterson
    Guest

    Re: Can you copy an Excel cell without the 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)?



    [email protected] wrote:
    >
    > 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?


    --

    Dave Peterson

  3. #3

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

    > 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.


  4. #4

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

    Dave Peterson wrote:
    > 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()


    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".


  5. #5

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

    Dave Peterson wrote:
    > 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()


    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


  6. #6
    Dave Peterson
    Guest

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

    Glad you got it working and customized the way you want.

    [email protected] wrote:
    >
    > Dave Peterson wrote:
    > > 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()

    >
    > 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


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1