Create a Dim that can hold both an Object and a String at the same time?

[Jake K]

I'm trying to create a command button that will copy preset data to clipboard. However, it needs to contain both a String and an Object. For example:

___________
This is the information you requested:

PART	DESCRIPTION
3735151	BATTERY

__________

Seems like I've tried everything under the Sun but, no luck. Here's as close as I can get:

Private Sub CommandButton32_Click()
Dim grD As Object
Set grD = Worksheets("TMP").Range("A1:B2")

Dim MyData As New DataObject
MyData.SetText "This is the information you requested:" & Chr(10) & grD
MyData.PutInClipboard
End Sub

If anyone can offer a solution, it would be greatly appreciated.

 

[ExcelToDAX]

How about:

Private Sub CommandButton32_Click()
Dim grD As Range
Set grD = Worksheets("TMP").Range("A1:B2")

Dim MyData As New DataObject
Dim cl As Range
Dim clipboardText As String

clipboardText = "This is the information you requested:" & Chr(10)

    ' Loop through each cl in the range, append to the clipboardText
For Each cl In grD
    clipboardText = clipboardText & cl.Value & vbTab
    If cl.Column = grD.Columns.Count Then
        clipboardText = clipboardText & vbCrLf
    End If
Next cl

MyData.SetText clipboardText
MyData.PutInClipboard
End Sub

 

[Jake K]

How about:

Private Sub CommandButton32_Click()
Dim grD As Range
Set grD = Worksheets("TMP").Range("A1:B2")

Dim MyData As New DataObject
Dim cl As Range
Dim clipboardText As String

clipboardText = "This is the information you requested:" & Chr(10)

    ' Loop through each cl in the range, append to the clipboardText
For Each cl In grD
    clipboardText = clipboardText & cl.Value & vbTab
    If cl.Column = grD.Columns.Count Then
        clipboardText = clipboardText & vbCrLf
    End If
Next cl

MyData.SetText clipboardText
MyData.PutInClipboard
End Sub

Thanks, ExcelToDAX.

That captures the data in the cells but, doesn't retain the actual cells and formating. It comes out like this when I paste:
__________
This is the information you requested:
PART
DESCRIPTION
3735151
BATTERY
__________

I was trying to figure out how to retain the actual cells:

PART	DESCRIPTION
3735151	BATTERY

Thanks for the effort though. Much appreciated.

 

[MARK858]

It comes out like this when I paste:

What program are you trying to paste into?

 

[Jake K]

Pasting it in email, (outlook).