putting data into clipboard

depcdivr

Active Member
Joined
Jan 21, 2008
Messages
304
Is it possible to put three textboxes worth of data into the clipboard so that they can be pasted into another program?

If you can do this, if the middle textbox is more than 1 line how can I put the 3rd textbox lined up with the top row from textbox2.

I.e. I am trying to copy

textbox1 value = part number
textbox2 value = description line 1
description line 2
description line 3
textbox3 value = price


I want to put this information into the clipboard so that when I go to paste it I get

part number description line 1 price
description line 2
description line 3
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Try this :-
Rich (BB code):
'=============================================================================
'- USE DATAOBJECT TO PUT TEXT INTO THE CLIPBOARD
'- useful for pasting into an external application
'- also see my code at http://www.mrexcel.com/forum/showthread.php?t=310991
'- Brian Baulsom July 2008
'=============================================================================
Private Sub CommandButton1_Click()
    Dim MyStr As String
    Dim MyDataObject As DataObject
    Dim Text2 As String
    Dim TextReturn As String
    Dim EndLine1 As Integer
    Dim MyText As String
    '-------------------------------------------------------------------------
    Set MyDataObject = New DataObject
    TextReturn = Chr(13)
    '-------------------------------------------------------------------------
    '- need to get first line of textbox into a separate string
    '- lines of a textbox are separated by hidden characters 13 & 10.
    '- ** these characters are retained in this example **
    '-------------------------------------------------------------------------
    '- End of line 1 : find first Return Character (code 13) in the string
    Text2 = TextBox2.Value
    EndLine1 = InStr(1, Text2, TextReturn, vbTextCompare)
    '-------------------------------------------------------------------------
    '- format the text string
    If EndLine1 = 0 Then        ' single line of text in textbox
        MyText = Text2
    Else
        MyText = Left(Text2, EndLine1 - 1) _
                & " " & TextBox3.Value _
                & Right(Text2, Len(Text2) - EndLine1)
    End If
    MsgBox (MyText)     ' for testing
    '-------------------------------------------------------------------------
    '- put text into the clipboard
    With MyDataObject
        .SetText MyText
        .PutInClipboard
    End With
    '-------------------------------------------------------------------------
    Set MyDataObject = Nothing
End Sub
'-----------------------------------------------------------------------------
 

depcdivr

Active Member
Joined
Jan 21, 2008
Messages
304
With sme modification this code worked well. Is there any way to insert a number of tabs in in front of each subsequent line so that the text will line up on lines 2 thru the end?
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Not sure exactly what you want.

This is how include a new line with required number of tabs with each addition (VBA contains system variables for Carriage Return(13), LineFeed(10) and Tab(9) as indicated below).

NB. This adds a final redundant "empty" line with tabs. Perhaps you could add code to remove it in the Form_Close event ? :-

Code:
'- PARTIAL CODE
'- Include new line after MyText with 2 Tabs
MyText = MyText & vbCr & vbLf & vbTab & vbTab
 

depcdivr

Active Member
Joined
Jan 21, 2008
Messages
304
What I am trying to do is to make the description on line 2+ line up with the description on line 1. Unfortunately there is a field that I need to report before the description. This field can be anywhere from 7 to 18 characters. This can span either 1 or two tabs widths so making it line up is proving to be a challange.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Could check the length of the extra text ... ?
eg. to add MyString

x=Len(MyString)
if x<10 Then ' add 2 tabs
MyText = MyText & vbCr & vbLf & vbTab & vbTab &MyString
Else ' add 1 tab
MyText = MyText & vbCr & vbLf & vbTab &MyString
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,122,388
Messages
5,595,889
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top